Only replace matching value at the end of string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Only replace matching value at the end of string

Author

Topic

twocups
Starting Member

3 Posts

Posted - 2012-08-09 : 09:43:10

I need to replace an entry at the end of a string, but only the occurence of the match at the end of the string and no where else.

finding the offending text with SELECT * FROM products_description WHERE products_description LIKE '% </td></tr><tr>'

How do I replace only the last matching value?

so example string is:

'<table><tr><td>blah</td</tr><tr><td>blah</td></tr><tr>'

I only want to replace the last occurence of '</td></tr><tr>'

Can find many examples matching single occurrences of strings, but not how to avoid multiple instances

Am sure this is straight forward, but hitting a wall.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 09:50:10

[code]
update products_description
set products_description = REVERSE(STUFF(REVERSE(products_description),1,PATINDEX(REVERSE('%</td></tr><tr>%'),products_description)+13,''))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

twocups
Starting Member

3 Posts

Posted - 2012-08-09 : 10:12:25

quote:

Originally posted by visakh16


update products_description 
set products_description = REVERSE(STUFF(REVERSE(products_description),1,PATINDEX(REVERSE('%</td></tr><tr>%'),products_description)+13,''))

Hi visakh16 - many thanks for the quick reply.

So using the above - I would put my replacement string 'foobar' in like so:

update products_description 
set products_description = REVERSE(STUFF(REVERSE(products_description),1,PATINDEX(REVERSE('%</td></tr><tr>%'),products_description)+13,'foobar'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 10:17:14

for replacing with new value do like

update products_description
set products_description = REVERSE(STUFF(REVERSE(products_description),1,PATINDEX(REVERSE('%</td></tr><tr>%'),products_description)+13,REVERSE('foobar')))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

twocups
Starting Member

3 Posts

Posted - 2012-08-09 : 11:00:20

Again thank you. Not quite there though (not sure if I have missed something

quote:
for replacing with new value do like

update products_description
set products_description = REVERSE(STUFF(REVERSE(products_description),1,PATINDEX(REVERSE('%</td></tr><tr>%'),products_description)+13,REVERSE('foobar')))

I get a

/* SQL Error (1305): FUNCTION testdatbase.STUFF does not exist */

Lots of references online of "SQL Error (1305): FUNCTION" for whitespace with this but cannot find issue or easy fix.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 11:20:49

are you using Microsoft SQL Server? i dont think so seeing your error

Please note that this is MS SQL server forum so we deal only with t-sql queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources