August 14, 2015 at 3:36 pm
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATE tbl
SET Body = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHERE tbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'
The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that... Is there a better way of doing this?
August 16, 2015 at 12:00 am
WHERE tbl.Body LIKE '%[^a-z]%'+pm.OldFirstName+'[^a-z]%' OR tbl.Body LIKE '%[^a-z]%'+pm.OldFirstName;
August 16, 2015 at 1:37 am
ErikMN (8/14/2015)
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATE tbl
SET Body = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHERE tbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'
The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that... Is there a better way of doing this?
Quick question, does the text you are searching in ever begin or end with the search word?
😎
August 16, 2015 at 6:07 am
Good question. I interpreted this question to mean that if the old first name was 'Ted,' then include '.Ted.' as well as '.Ted'
August 17, 2015 at 9:46 am
WHERE tbl.Body + '.' LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 17, 2015 at 11:12 am
ScottPletcher (8/17/2015)
WHERE tbl.Body + '.' LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'
Thanks! It took me a sec to figure out what you were doing (it's Monday morning) but it's exactly what I needed. It's also one of those solutions that seems so obvious after you see the answer. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply