SQL Like Clause

Question

SQL Like Clause

Viewing 7 posts - 31 through 36 (of 36 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Jeff Moden (3/13/2012)
anthony.green (3/12/2012)
you might want to take a look at full text indexing and the contains function then, otherwise you will be wrapping the variable in a multiple of replace statements for 2%'s 3%'s 4%'s etc etc it will just become hard to read properly.
Have you ever deployed FTS(Full Text Searches) before? Setting it up and maintaining the indexes aren't the joy you might think. 😛

I've set up many, and never found it to be all that big a deal. Works great for this kind of thing. And much faster than Like or CharIndex solutions if you have any volume of data at all.

I'm in the middle of a solution that uses a custom FullText thesaurus to detect that:

Bob Smith

1515 N 1st Ave #15

Anytown, Anystate USA

is the same as:

Robert Smith

1515 No. 1st Ave. Apt. 15

Anytown, Anystate, US

Five-million rows of data, query-time in milliseconds, and the index is nearly trivial in terms of disk space. No big deal to set up, no big deal to maintain, works beautifully.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 2

Jeff Moden (3/14/2012)
anthony.green (3/14/2012)
Jeff Moden (3/13/2012)
anthony.green (3/12/2012)
you might want to take a look at full text indexing and the contains function then, otherwise you will be wrapping the variable in a multiple of replace statements for 2%'s 3%'s 4%'s etc etc it will just become hard to read properly.
Have you ever deployed FTS(Full Text Searches) before? Setting it up and maintaining the indexes aren't the joy you might think. 😛
yeah I have used full text catalogs and indexes in the past but only on a small scale < 100000 rows in the tables, touch wood, not had any issues, the automatic update did what we needed to do without any issues
Where you able to use it to expedite partial searches using CONTAINS to do partial word matches similar to LIKE '%something%"?

It won't do that. Not if you mean searching a string like "thisisalongstringwithmanycharacters" for "string" as a sub. For that, you pretty much have to use Like/PatIndex/CharIndex. But if what you want is "Bob" out of "Joe Bob" or "Billy Bob", then yes it can do that, and faster than Like or CharIndex on any significant number of rows.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

The setup and maintenance on full-text indexes is not trivial.

It's not a problem for a full-time DBA, but if you're not a DBA, it can be a tricky thing, especially if an error occurs.

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".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

GSquared (3/15/2012)
Jeff Moden (3/14/2012)
anthony.green (3/14/2012)
Jeff Moden (3/13/2012)
anthony.green (3/12/2012)
you might want to take a look at full text indexing and the contains function then, otherwise you will be wrapping the variable in a multiple of replace statements for 2%'s 3%'s 4%'s etc etc it will just become hard to read properly.
Have you ever deployed FTS(Full Text Searches) before? Setting it up and maintaining the indexes aren't the joy you might think. 😛
yeah I have used full text catalogs and indexes in the past but only on a small scale < 100000 rows in the tables, touch wood, not had any issues, the automatic update did what we needed to do without any issues
Where you able to use it to expedite partial searches using CONTAINS to do partial word matches similar to LIKE '%something%"?
It won't do that. Not if you mean searching a string like "thisisalongstringwithmanycharacters" for "string" as a sub. For that, you pretty much have to use Like/PatIndex/CharIndex. But if what you want is "Bob" out of "Joe Bob" or "Billy Bob", then yes it can do that, and faster than Like or CharIndex on any significant number of rows.

That's where I was going with all of this and thank you very much for the confirmation, Gus. I was really surprised to see anyone that had actually used it recommend it for basic '%something%' searches.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Possinator Hall of Fame Points: 3422 More actions · Answer 5

Well, I think

SELECT * FROM dbo.mytable WHERE CHARINDEX(@FirstName,FirstName) > 0

is just gorgeous. It never ever occurred to me to do such a thing. Shucks.

Man I love this place.

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 6

Jeff Moden (3/15/2012)
GSquared (3/15/2012)
Jeff Moden (3/14/2012)
anthony.green (3/14/2012)
Jeff Moden (3/13/2012)
anthony.green (3/12/2012)
you might want to take a look at full text indexing and the contains function then, otherwise you will be wrapping the variable in a multiple of replace statements for 2%'s 3%'s 4%'s etc etc it will just become hard to read properly.
Have you ever deployed FTS(Full Text Searches) before? Setting it up and maintaining the indexes aren't the joy you might think. 😛
yeah I have used full text catalogs and indexes in the past but only on a small scale < 100000 rows in the tables, touch wood, not had any issues, the automatic update did what we needed to do without any issues
Where you able to use it to expedite partial searches using CONTAINS to do partial word matches similar to LIKE '%something%"?
It won't do that. Not if you mean searching a string like "thisisalongstringwithmanycharacters" for "string" as a sub. For that, you pretty much have to use Like/PatIndex/CharIndex. But if what you want is "Bob" out of "Joe Bob" or "Billy Bob", then yes it can do that, and faster than Like or CharIndex on any significant number of rows.
That's where I was going with all of this and thank you very much for the confirmation, Gus. I was really surprised to see anyone that had actually used it recommend it for basic '%something%' searches.

You're welcome.

But the question becomes: Are users actually searching for "ob" in order to find "Bob", "Robert", "Aboobacker", "Baobinh", et al, or are they searching for "Bob" in order to find "Billy Bob" and the like? (Those are all actual names with "ob" in them. Out of a 5-million name table, I have 885 distinct names with "ob" in them.) I can't think of an actual use of the true-substring search, but finding "Bob Smith" by searching an FTI with names in the thesaurus file, and finding "Bob Smith", "Robert Smith", "Bobby Smith", "Robby Smith", "Bert Smith", and "Bo Smith" (all valid variations on "Bob") is quite valuable for businesses.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 7

Chris Hurlbut

SSCarpal Tunnel

Points: 4162

March 16, 2012 at 2:45 pm

#1460782

Charindex is how I would roll...