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
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
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');
| COLUMN1 | REGEXP_REPLACE(COLUMN1, '(^[^\\]+\\)([^\\]+)\\', '\1OTHERWORD\\', 1, 1, 'E') |
|---|---|
| D:\SomeFolder\File1.jpg | D:\OtherWord\File1.jpg |
| D:\SomeFolder\File2.jpg | D:\OtherWord\File2.jpg |
| D:\SAMEFolder\File2.jpg | D:\OtherWord\File2.jpg |
| D:\SomeFolder\File3.jpg | D:\OtherWord\File3.jpg |