CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

How to replace string from a snowflake table

I have a snowflake table as TEST_TABLE

SOURCE_RECORD_ID                                 COL_A  COL_B COL_C
paragon_altdata:model:2021-04-29_003108:1          A     A     A
paragon_altdata:model:2021-04-29_003108:2          A     A     A
paragon_altdata:model:2021-04-29_003108:3          A     A     A
paragon_altdata:model:2021-04-29_003108:4          A     A     A
paragon_altdata:model:2021-04-29_003108:5          A     A     A
paragon_altdata:model:2021-04-29_003108:6          A     A     A
paragon_altdata:model:2021-04-29_003108:7          A     A     A
paragon_altdata:model:2021-04-29_003108:8          A     A     A
paragon_altdata:model:2021-04-29_003108:9          A     A     A

In this table i want to replace partial string paragon_altdata to paragonintel_altdata from the first column

My table should look like below

SOURCE_RECORD_ID                                 COL_A  COL_B COL_C
paragonintel_altdata:model:2021-04-29_003108:1         A     A     A
paragonintel_altdata:model:2021-04-29_003108:2         A     A     A
paragonintel_altdata:model:2021-04-29_003108:3         A     A     A
paragonintel_altdata:model:2021-04-29_003108:4         A     A     A
paragonintel_altdata:model:2021-04-29_003108:5         A     A     A
paragonintel_altdata:model:2021-04-29_003108:6         A     A     A
paragonintel_altdata:model:2021-04-29_003108:7         A     A     A
paragonintel_altdata:model:2021-04-29_003108:8         A     A     A
paragonintel_altdata:model:2021-04-29_003108:9         A     A     A

Please anyone help me on this

Upvotes: 0

Views: 1288

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

You can just use replace and then to create a new table do a create table as select

create NEW_TABLE as 
select 
     replace(SOURCE_RECORD_ID, 'paragon_', 'paragonintel_') as SOURCE_RECORD_ID
    ,COL_A
    ,COL_B
    ,COL_C
from TEST_TABLE
;

You can then swap them and drop the old one.

Upvotes: 1

Related Questions