whole word LIKE with parameter

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 2005 Forums

Transact-SQL (2005)

whole word LIKE with parameter

Author

Topic

Angate
Starting Member

24 Posts

Posted - 2011-01-02 : 20:05:58

I have a stored procedure that accepts a variable length parameter for the beginning of the first OR last name of a user. What I want is for the LIKE I use to search user first and last names for the entire parameter, not just a portion of it.

ALTER PROCEDURE dbo.search

	(
	@searchText nvarchar
	)
	
AS
	SET NOCOUNT ON

	SELECT DISTINCT * 
	FROM (

	SELECT userID, firstName, lastName
	  FROM userTable
	 WHERE firstName LIKE @searchText + '%'

	 	 UNION

	 SELECT userID, firstName, lastName
	   FROM userTable
	  WHERE lastName LIKE @searchText + '%'

	 ) as users

	RETURN

If I have two users, 'John Doe' and 'Jane Doe' and do a search for 'Jo', both are returned because both start with a 'J', but my intention would be to only have 'John Doe' returned.

Any Ideas? I have searched all over and can't find anything different than what I have done.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-01-02 : 20:57:57

You should specify the length of NVARCHAR parameter @searchText to suit your data.

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 04:27:03

"You should specify the length of NVARCHAR parameter @searchText to suit your data"

I agree strongly with that

.

"If I have two users, 'John Doe' and 'Jane Doe' and do a search for 'Jo', both are returned because both start with a 'J"

That won't happen. If you search for "Jo" with that query you will NOT get "Jane Doe"

However, you will get "John Doe" and your Subject says you only want whole words, in which case this may help:


WHERE firstName LIKE @searchText + '%'
  AND firstName + ' ' LIKE @searchText + ' %'

do likewise for lastName.

I have added this to your WHERE clause, rather than just replacing your WHERE clause (which would also work), as I think this method will be index-friendly.

However, this will not match "Jo-Anne" nor "Jo." etc.

You could catch those with:


WHERE firstName LIKE @searchText + '%'
  AND firstName + ' ' LIKE @searchText + '[- .]%'

but you will have to include all possible "terminator" characters in the "closure".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:29:31

See what happens when you don't specify the length
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2011-01-03 : 05:56:26

quote:
"If I have two users, 'John Doe' and 'Jane Doe' and do a search for 'Jo', both are returned because both start with a 'J"

That won't happen. If you search for "Jo" with that query you will NOT get "Jane Doe"

Its exactly opposite of what you are saying if you dont specify the length for the parameter or the variable.

Try this


declare @t table(name nvarchar(20))
insert @t
select 'John Doe' union
select 'Jane Doe' 

declare @searchText nvarchar
set @searchText='Jo'
select * from @t where name like   @searchText +'%'

PBUH

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-04 : 19:53:43

If you don't specify the length of a varchar/nvarchar, the length defaults to one. Your "JO" converts implicitly to "J" and matches "John" and "Jane".

Moral: Don't be a "JO"; specify your variable size.

(Sorry, but it was just too delicious to resist)

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 02:14:07

quote:
Originally posted by Bustaz Kool

If you don't specify the length of a varchar/nvarchar, the length defaults to one. Your "JO" converts implicitly to "J" and matches "John" and "Jane".

Moral: Don't be a "JO"; specify your variable size.

(Sorry, but it was just too delicious to resist)

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

The default length depends on the usage. Refer the link I posted in my previous reply

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 03:14:27

quote:
Originally posted by Sachin.Nand

Its exactly opposite of what you are saying if you dont specify the length for the parameter or the variable.

Thanks. Hadn't realised that an NVARCHAR with no size would have that effect, but makes sense now you say it.

SQL should treat that as an error, or at least raise a warning (just like with the MESS that is implicit date conversion ... :() ... but if MS was bothered to do it at all they would have done it by now

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 05:10:40

<<
SQL should treat that as an error
>>

I expect this error

Msg 8152, Level 16, State 14, Line 4 String or binary data would be truncated.The statement has been terminated.

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 06:15:39

"I expect this error"

Indeed, but why not enforce that NVARCHAR must ALWAYS have a size parameter? Seems utterly daft to me that the original specification allowed it (maybe it was a bug in Version-1 which has remained forever :()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 08:04:58

quote:
Originally posted by Kristen

"I expect this error"

Indeed, but why not enforce that NVARCHAR must ALWAYS have a size parameter? Seems utterly daft to me that the original specification allowed it (maybe it was a bug in Version-1 which has remained forever :()

It is not a bug but a feature which is documented

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-05 : 08:08:48

"It is not a bug but a feature which is documented"

Hahaha ... well that's all right then

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources