I have a column with data as:

D:\SomeFolder\File1.jpg
D:\SomeFolder\File2.jpg
D:\SomeFolder\File3.jpg

How do I replace the characters using a SQL query such that column is updated like this:

E:\DifferentFolder\File1.jpg
E:\DifferentFolder\File2.jpg
E:\DifferentFolder\File3.jpg

CodePudding user response：

If you wan to match the first word use:

SELECT column1
    ,REGEXP_REPLACE(column1, '(^[^\\\\] \\\\)([^\\\\] )\\\\', '\\1OtherWord\\\\', 1, 1, 'e')
FROM VALUES 
    ('D:\\SomeFolder\\File1.jpg'),
    ('D:\\SomeFolder\\File2.jpg'),
    ('D:\\SAMEFolder\\File2.jpg'),
    ('D:\\SomeFolder\\File3.jpg');

CodePudding user response：

You can try to use replace function.

select replace(val, 'SomeFolder','DifferentFolder') from T

CodePudding user response：

In snowflake, you can use

REPLACE( <subject> , <pattern> [ , <replacement> ] )

So in your case

REPLACE(<column_name>, 'SomeFolder', 'DifferentFolder') 

should do the trick.

https://docs.snowflake.com/en/sql-reference/functions/replace.html