Sql Query to find like matches.

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

SQL Server 2000 Forums

SQL Server Development (2000)

Sql Query to find like matches.

Author

Topic

jrlanders
Starting Member

15 Posts

Posted - 2003-09-23 : 15:13:11

Hi all,

Using a Stored Procedure called from an ASP Page.
What I am trying to accomplish is this. If there is a name that I am not exactly sure exists in the data base. I would like to return anything that matches Marc Thompson. Ex: Mar* Thomp*
wild card meaning of course anything after the r in Mar and anything after the p in Thomp...

Code:

@Page int,
@RecsPerPage int,
@ReqrFirst nvarchar(70),
@ReqrLast nvarchar(70)

AS

Set NOCOUNT on

Create Table #TempItems
(
ID int IDENTITY,
FSRNum nvarchar(70),
ClientName nvarchar(70),
Reqr nvarchar(70),
ConsultantSch nvarchar(70),
DateSch datetime,
ReqrFirst nvarchar(70),
ReqrLast nvarchar(70),
MailAddr nvarchar(70),
Region int,
ProcessedFlag bit

)

Insert INTO #TempItems (FSRNum,ClientName,Reqr,ConsultantSch,DateSch,ReqrFirst,ReqrLast,MailAddr,Region,ProcessedFlag)
Select FSRNum,ClientName,Reqr,ConsultantSch,DateSch,ReqrFirst,ReqrLast,MailAddr,Region,ProcessedFlag FROM Presales
WHERE @ReqrFirst Like ReqrFirst and @ReqrLast Like ReqrLast
Order BY ReqrLast

Declare @FirstRec int, @LastRec int
Select @FirstRec = (@Page -1) * @RecsPerPage
Select @LastRec = (@Page * @RecsPerPage +1)

Select *,
MoreRecords =
(
Select COUNT (*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

Set NOCOUNT off

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-23 : 15:18:26

And your question is ?????

(i'll take a stab: you know the LIKE operator in T-SQL uses % as a wildcard match, and not *, right ? )

- Jeff

jrlanders
Starting Member

15 Posts

Posted - 2003-09-23 : 15:31:06

LOL! Yeah I guess I forgot that part - the question.

I have tried the % and still it will not find a match. I know Marc Thompson is in the records. If I type in the complete Name 'Marc Thompson' it will find the records. I beleive I have the SQL statement coded correctly. I just cannot pull back partial matches...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-23 : 15:35:05

i think your WHERE clause is backwards.

instead of:

WHERE @ReqrFirst Like ReqrFirst and @ReqrLast Like ReqrLast

you need:

WHERE ReqrFirst like @ReqrFirst and ReqrLast Like @ReqrLast

see if that helps ... (in addition to using % 's ...)

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 15:40:42

Jeff, you can do either way, but the second way is the optimized way from what I understand.

jrlanders, you need to concatenate your percent sign in the variable. Check out the SET statement below:


SET NOCOUNT ON

DECLARE @ReqrFirst VARCHAR(50)
DECLARE @ReqrLast VARCHAR(50)

SET @ReqrFirst = 'Tar' + '%'
SET @ReqrLast = 'Dug' + '%'


CREATE TABLE Presales
(
ReqrFirst VARCHAR(50) NOT NULL,
ReqrLast VARCHAR(50) NOT NULL
)

INSERT INTO Presales VALUES ('Tara', 'Duggan')
INSERT INTO Presales VALUES ('Michael', 'Smith')

SELECT ReqrFirst, ReqrLast
FROM Presales 
WHERE ReqrFirst LIKE @ReqrFirst AND ReqrLast LIKE @ReqrLast
Order BY ReqrLast

DROP TABLE Presales

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 15:43:41

quote:
Originally posted by jsmith8858
see if that helps ... (in addition to using % 's ...)

I would think that most importantly...


USE Northwind
GO

DECLARE @x varchar(25)
SELECT @x = 'A'
SELECT * FROM Employees WHERE FirstName Like @x
--As compared to
SELECT * FROM Employees WHERE FirstName Like @x + '%'

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jrlanders
Starting Member

15 Posts

Posted - 2003-09-23 : 15:48:28

I Move the Fields and variables around as Jeff Indicated and got instant matches. That worked perfectly.

At the risk of being greedy.

How do I ignore what cast the user types in and what is in the database?

User enters: marc thompson
database entry is Marc Thompson.

That is still correct match... but does not catch it do to the case mismatch.

Suggestions?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 15:50:23

You set your database as case sensitive? Or are you asking how to store the data in format you want?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 15:52:22

Moving the fields around wouldn't have fixed the problem, you added the % in the query. You can actually have the fields in either order, just one is optimized.

If the column's collation is case-insensitive, then it doesn't matter. If is case-sensitive, then check out LCASE or UCASE in SQL Server Books Online.

Tara

jrlanders
Starting Member

15 Posts

Posted - 2003-09-23 : 16:00:27

Yes.. the DB is case sensitive. I will check out the U and L case SQL command.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 16:02:38

It should have said LOWER and UPPER instead of LCASE and UCASE.

Why didn't you use the default collation for the database? It is so much easier if you don't have requirements to do it another way.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-23 : 16:10:26

About switching the order of the operands in the LIKE statements:

try running this in QA:


select 1 where 'hey' like 'hey%'

select 1 where 'hey%' like 'hey'

notice the first works, the second doesn't.

In order words,

"A like B" does NOT imply "B like A"

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 16:11:30

Little late now....


USE Northwind
GO

DECLARE @x varchar(25)
SELECT @x = 'A'
SELECT * FROM Employees WHERE UPPER(FirstName) Like UPPER(@x) + '%'

Can you say SCAN?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 16:21:47

quote:
Originally posted by jsmith8858
In order words,

"A like B" does NOT imply "B like A"

More like:

"A Like B_something"

Does not imply

"B_Something Like A"

No?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 17:07:38

Ah yes, I was thinking of equals and not LIKE. Sorry about the confusion.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources