SQL Like Clause

Question

SQL Like Clause

Viewing 15 posts - 16 through 30 (of 36 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

jcb (3/12/2012)
Jared,
I just point that out because a user can input "",".","mary" or anything can fetch too many records depending on OP data.

That's true, but I would handle that from the application end probably. I would never want to limit with a TOP for a search because the record being searched for may not be in that TOP. There are other ways to limit the results on the application by saying that "too many results would be returned, so narrow your search" or by filtering on an id or something to page out filtered results. However, to actually return results from a TOP seems pointless to me and will break most business cases anyway.

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQLKnowItAll (3/12/2012)
I think now that the OP knows that % must be in brackets when being used as a literal, the issue is fixed.

It doesn't have to be in brackets, there's at least one other way of escaping special characters for use in a LIKE.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jcb SSCertifiable Points: 6574 More actions · Answer 3

Its depends largely of the app.

If a user is searching a phone number with a like and he inputs "99".

"showing the first 1.000 of 10.000.000 numbers" can be a option since is useless show more than that.

Of course it can be a interface option.

For these kind of tasks I prefer server-side paging but sometimes a top can be used in the sake of simplification (user interface simplification, not lazy coding simplification :-P).

Jared SSC Guru Points: 61793 More actions · Answer 4

GilaMonster (3/12/2012)
SQLKnowItAll (3/12/2012)
I think now that the OP knows that % must be in brackets when being used as a literal, the issue is fixed.
It doesn't have to be in brackets, there's at least one other way of escaping special characters for use in a LIKE.

Good to know. Care to elaborate and share that other way? 🙂

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

From BOL:

C. Using the ESCAPE clause

The following example uses the ESCAPE clause and the escape character to find the exact character string 10-15% in column c1 of the mytbl2 table.

USE tempdb;

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'mytbl2')

DROP TABLE mytbl2;

GO

USE tempdb;

GO

CREATE TABLE mytbl2

(

c1 sysname

);

GO

INSERT mytbl2 VALUES ('Discount is 10-15% off');

INSERT mytbl2 VALUES ('Discount is .10-.15 off');

GO

SELECT c1

FROM mytbl2

WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';

GO

Jared SSC Guru Points: 61793 More actions · Answer 6

Lynn Pettis (3/12/2012)
From BOL:
C. Using the ESCAPE clause
The following example uses the ESCAPE clause and the escape character to find the exact character string 10-15% in column c1 of the mytbl2 table.
USE tempdb;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2;
GO
USE tempdb;
GO
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off');
INSERT mytbl2 VALUES ('Discount is .10-.15 off');
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';
GO

Nothin' like having someone else do the work for me. 😀 Thanks Lynn!

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLKnowItAll (3/12/2012)
GilaMonster (3/12/2012)
SQLKnowItAll (3/12/2012)
I think now that the OP knows that % must be in brackets when being used as a literal, the issue is fixed.
It doesn't have to be in brackets, there's at least one other way of escaping special characters for use in a LIKE.
Good to know. Care to elaborate and share that other way? 🙂

Books Online not installed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 8

GilaMonster (3/12/2012)
SQLKnowItAll (3/12/2012)
GilaMonster (3/12/2012)
SQLKnowItAll (3/12/2012)
I think now that the OP knows that % must be in brackets when being used as a literal, the issue is fixed.
It doesn't have to be in brackets, there's at least one other way of escaping special characters for use in a LIKE.
Good to know. Care to elaborate and share that other way? 🙂
Books Online not installed?

I just thought this forum topic was a good place for it and sometimes asking someone who knows gets better performance than trying to find it myself. I suppose I am just lazy today.

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 9

marly (3/12/2012)
seth delconte (3/12/2012)
marly (3/12/2012)
I changed it to
SELECT * from dbo.mytable WHERE FirstName LIKE ‘%’ + REPLACE(@FirstName , ‘%’,’’) + ‘%’ but it still seems to put down the whole database if I do a %%
What do you mean 'put down the whole database'? Does it hang, or does it err?
Do you get the same results if the text box is empty?
Sorry it's a typo. I mean, it will "pull down the whole database", as in it pulls all records from the DB.
I was hoping there would be an easy fix in the stored procedure, then I only need to change the stored procedure and not the application.

I believe we're using the wrong tool here. You're looking for any value of @FirstName in the FirstName column. You don't need "%" filters at all. If the user enters a "%" in the following, they'll only get rows that contain a "%".

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

--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)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

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

--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)

Ant-Green SSC Guru Points: 113445 More actions · Answer 11

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 12

small typo in the code from Jeff Moden...

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

should be

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

:hehe:

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

HanShi (3/14/2012)
small typo in the code from Jeff Moden...
SELECT * FROM dbo.mytable WHERE CHARINDEX(@FirstName,FirstName) + 0
should be
SELECT * FROM dbo.mytable WHERE CHARINDEX(@FirstName,FirstName) > 0
:hehe:

Thank you very much for the catch and the correction. :blush: It's amazing how close the "+" and ">" keys are to each when you've run out of coffee. 🙂

I've corrected the original post.

--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)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 14

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%"?

--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)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 15

I wouldn't automatically add a leading %. That forces a full scan which is often unnecessary if the requestor knows what the first name starts with.

Instead, allow the user to specify a leading wildcard char only if he/she wants to.

I usually allow "*" as well as "%", since many people are used to using "*" for a wildcard. Of course REPLACE "*" with a "%" in the SQL itself.

And, in case you have or add an index on FirstName, I would use the style:

FirstName LIKE @FirstName + '%'

rather than:

CHARINDEX(@FirstName,FirstName) > 0

because SQL can directly use an index for the first format, whereas for the second it cannot. Even an index scan is not quite as bad as a table scan.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.