CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shruti
Shruti

Reputation: 135

REPLACE in Snowflake

I am trying to do the following, I want to replace only the first "T_" from a string value. However with REPLACE, it will replace all the "T_" occurrences

REPLACE('T_DEV_ABCT_FACT_SALEST_TEST', 'T_')

Is there any other way of doing this so that it replace only and only the first occurrence ?

Thank You,

Upvotes: 0

Views: 677

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

if you have to replace the "first occurrence" then Tim's will only match "when the occurrence is at the start", luckily REGEXP_REPLACE has some options to help with this, the first extra parameter says, only replace 1 instance (the second 1 in this case) and there are some other hack methods like INSERT/POSITION (that don't work so good)

SELECT 
    column1 as val
    ,REPLACE(val, 'T_', 'x') as first_try
    ,REGEXP_REPLACE(val, '^T_', 'x') AS only_first_token
    ,REGEXP_REPLACE(val, 'T_', 'x', 1, 1) AS first_t_only
    ,POSITION('T_', val) as pos
    ,INSERT(val, POSITION('T_', val),iff(POSITION('T_', val) > 0, 2,0), 'x') as insert_hack
FROM VALUES
    ('T_LINE_START_WITH_T'),
    ('LINE_NOT_START_WITH_T'),
    ('LINE_NO_TEE_UNDERSCORE')
    ;

gives:

VAL FIRST_TRY ONLY_FIRST_TOKEN FIRST_T_ONLY POS INSERT_HACK
T_LINE_START_WITH_T xLINE_STARxWITH_T xLINE_START_WITH_T xLINE_START_WITH_T 1 xLINE_START_WITH_T
LINE_NOT_START_WITH_T LINE_NOxSTARxWITH_T LINE_NOT_START_WITH_T LINE_NOxSTART_WITH_T 8 LINE_NOxSTART_WITH_T
LINE_NO_TEE_UNDERSCORE LINE_NO_TEE_UNDERSCORE LINE_NO_TEE_UNDERSCORE LINE_NO_TEE_UNDERSCORE 0 xLINE_NO_TEE_UNDERSCORE

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use a regex replacement:

WITH t AS (
    SELECT 'T_DEV_ABCT_FACT_SALEST_TEST' AS val
)

SELECT val, REGEXP_REPLACE(val, '^T_', 'F_') AS val_out
FROM t;

-- T_DEV_ABCT_FACT_SALEST_TEST, F_DEV_ABCT_FACT_SALEST_TEST

Upvotes: 1

Related Questions