CODEWITHSUNDEEP
sqlpostgresql
rajani chowdhary
rajani chowdhary

Reputation: 175

postgresql replace function using pattern matching characters

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

Upvotes: 4

Views: 4556

Answers (1)

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 2

Related Questions