postgresql replace function using pattern matching characters

Question

I am having a table named "OptionsDetail" with column named "URL" in postgresql database. The "URL" column contain the following data

URL

http://www.site1.com/ebw/file1/detail.action?cid=1
http://www.another2.com/ebw/file1/detail.action?cid=11&code=MYCODE
http://www.anothersite3.com/ebw/file1/detail.action?cid=12&code=ANOTHERCODE&option=ROOM

Now I want to replace the data in URL to

URL

/file1/detail.action?cid=1
/file1/detail.action?cid=11&code=MYCODE
/file1/detail.action?cid=12&code=ANOTHERCODE&menu=ROOM

I wrote the following query to perform the above functionality

 UPDATE  "OptionsDetail" SET "URL" = replace("URL",'http://%/ebw/file1','/file1') WHERE  "URL" LIKE  '%/ebw/file1%';

And also another way I wrote like this

  UPDATE  "OptionsDetail" SET "URL" = replace("URL",'%/ebw/file1','/file1') WHERE  "URL" LIKE  '%/ebw/file1%';

Query is executing successfully saying like for ex: 200 rows affected but "URL" column data is not changing the way I need, the data is remaining as it is.

Please help me to resolve this issue

replace doesn't support wildcards. You want regexp_replace — user330315
– user330315, Commented Nov 9, 2015 at 6:40

Andomar · Accepted Answer · 2015-11-09 06:45:11Z

3

The problem is that replace doesn't support wildcards like %:

replace("URL",'http://%/ebw/file1','/file1')
                     ^^^

You can use regexp_replace instead:

UPDATE  YourTable
SET     URL = regexp_replace(URL, 'http://.*/ebw/file1', '/file1') 
WHERE   URL LIKE '%/ebw/file1%'

Note that regexp_replace uses different wildcards than like. In regular expressions, "Any number of any character" is .* instead of %.

See it working at SQL Fiddle.

answered Nov 9, 2015 at 6:45

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Candocia Over a year ago

One thing I noticed is that if you use % instead of . in regexp_replace, it acts as a non-capturing wildcard character, so anything that it matches won't be replaced.

Collectives™ on Stack Overflow

postgresql replace function using pattern matching characters

URL

URL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

URL

URL

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related