9.String Transformations.sql

use ecom;
-- STRING TRANSFORMATIONS

select * from dim_customer;

-- Type casting

select
	customer_key,
    cast(customer_key as char(1000)),-- Application : while joining tables, the column type should also match.
    cast((cast(customer_key as char(1000))) as float)
from
	dim_customer;
    
-- String functions

-- Concatenate
select
	*,
	concat(first_name,' ',last_name) as 'name',  -- can use multiple columns
    concat_ws(' ',first_name,last_name) as 'wsname', -- seperator need to be mentioned only once
    length(country) as country_size, -- length of a column
    lower(city), -- convert to lower case
    substring(email,1,6), -- slicing start from 1, take 6 alphabets
    replace(email,'@','%'), -- replace characters
    left(country,3), -- 3 characters from left
    right(country,3), -- 3 characters from right
    reverse(country), -- reverse the country column
    repeat(first_name,2) -- repeat value 2 times
from
	dim_customer;