LIKE, [ ] and empty string

Question

Post reply

LIKE, [ ] and empty string

ErikMN

SSChasing Mays

Points: 603
More actions
August 14, 2015 at 3:36 pm

#310898

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.
UPDATEtbl
SETBody = 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
WHEREtbl.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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ARROWW SSC Enthusiast Points: 128 More actions · Answer 1

WHEREtbl.Body LIKE '%[^a-z]%'+pm.OldFirstName+'[^a-z]%' OR tbl.Body LIKE '%[^a-z]%'+pm.OldFirstName;

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 2

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.
UPDATEtbl
SETBody = 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
WHEREtbl.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?

😎

ARROWW SSC Enthusiast Points: 128 More actions · Answer 3

Good question. I interpreted this question to mean that if the old first name was 'Ted,' then include '.Ted.' as well as '.Ted'

ScottPletcher SSC Guru Points: 101129 More actions · Answer 4

WHEREtbl.Body + '.' LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ErikMN SSChasing Mays Points: 603 More actions · Answer 5

ScottPletcher (8/17/2015)
WHEREtbl.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. 😀