How to split the column into multiple column and insert new column into same table in snowflake-CodePudding

I am trying to split column into mulitple column and insert into same table using snowflake query.

EMP Table

ID NAME         AGE
1  Ravi#Kumar   25
2  Virat#Singh  26
3  Rohit#Sharma 27

EMP Table after split

ID  NAME           F_NAME   L_NAME  AGE
1   Ravi#Kumar     Ravi     Kumar   25
2   Virat#Singh    Viart    Singh   26
3   Rohit#Sharma   Rohit    Sharma  27

I am able to select the data and spilt but I wanted to alter the existing table only.

CodePudding user response：

We can use REGEXP_SUBSTR() here:

SELECT
    ID,
    NAME,
    REGEXP_SUBSTR(NAME, '([^#] )', 1, 1, 'e', 1) AS F_NAME,
    REGEXP_SUBSTR(NAME, '([^#] )', 1, 2, 'e', 1) AS L_NAME,
    AGE
FROM yourTable;

If you actually want to update your table, then add the F_NAME and L_NAME columns, and then use:

UPDATE yourTable
SET F_NAME = REGEXP_SUBSTR(NAME, '([^#] )', 1, 1, 'e', 1),
    L_NAME = REGEXP_SUBSTR(NAME, '([^#] )', 1, 2, 'e', 1);

CodePudding user response：

Create source data.

create or replace table emp_source as 
select ID, NAME, AGE 
from (values 
        (1,  'Ravi#Kumar' , 25),
        (2,  'Virat#Singh',   26),
        (3,  'Rohit#Sharma', 27) t (ID, NAME, AGE)
     );

We can split NAME using STRTOK as a slightly simpler alternative to REGEX_SUBSTR suggested by Tim Biegeleisen.

Select 
   ID, 
   strtok(NAME, '#',1) f_name, 
   strtok(NAME, '#',2) l_name, 
   AGE
from emp_source;

You could use computed/derived columns for this as the source column NAME contains the data you need per row: computed/derived columns

create or replace table emp 
(id bigint, 
 name varchar,  
 f_name varchar as strtok(NAME, '#',1),
 l_name varchar as strtok(NAME, '#',2),
 age integer
 )
 ;

Insert the 3 source columns from source data into EMP table.

 Insert into emp 
 Select id,name,age from emp_source;

Query EMP and the computed/Derived columns are calculated on the fly

 Select * from emp;

If your EMP table is already created you could use alter table to switch them to derived/computed columns.

# Drop current columns if they already exist in table
alter table emp drop column f_name;
alter table emp drop column l_name;
# Add derived/computed columns
alter table emp add column f_name varchar as strtok(NAME, '#',1);
alter table emp add column l_name varchar as strtok(NAME, '#',2);

Now when you query the table the column values will be computed on the fly and returned, and you don't need to Update the table.

Note: No metadata is gathered on those columns, so if queries are likely to filter on them you may be better deriving them at Insert time or updating them. Also those columns will be computed for every query that selects them, so if they are used a lot it may also be better to UPDATE the table with the physical values