Querying by First Letter? - SQL Server Forums

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)

Querying by First Letter?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-01-18 : 12:20:03

I need to display user's in an application I am building by the first letter of their last name. Would it be better to query with LIKE, or directly check the first letter?


WHERE U.LastName LIKE @FirstLetter + '%'

WHERE LOWER(SUBSTRING(U.LastName, 1, 1)) = LOWER(@FirstLetter)

The DB is not case-senstive so technically the LOWER()s aren't required. Also, what if I needed a range, say "An" through "Az"? The best I could come up with was this:


WHERE
	ASCII(LOWER(SUBSTRING(U.LastName, 1, 1))) >= ASCII(LOWER(@StartFirstLetter))
	AND ASCII(LOWER(SUBSTRING(U.LastName, 1, 1))) <= ASCII(LOWER(@EndFirstLetter))
	AND ASCII(LOWER(SUBSTRING(U.LastName, 2, 1))) >= ASCII(LOWER(@StartSecondLetter))
	AND ASCII(LOWER(SUBSTRING(U.LastName, 2, 1))) <= ASCII(LOWER(@EndSecondLetter))

Thanks,
Steve

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-18 : 14:51:06

There is more than 1 way to skin a cat

quote:
I need to display user's in an application I am building by the first letter of their last name. Would it be better to query with LIKE, or directly check the first letter?v

Both of the WHERE's you wrote look fine. The execution plan on the LIKE surprisingly shows a nested outer loop to me. I guess it's slower.

Here's another that generates the same execution plan but works for any length string without using LIKE

(LEFT doesn't pull an error if the string is shorter than the number of characters requested. LEFT('AB', 5) is valid and returns 'AB' - no blanks to the right)

SELECT Username
FROM Users
WHERE LEFT(Username, LEN(@Firstletter)) = @Firstletter
ORDER BY Username

quote:
what if I needed a range, say "An" through "Az"?


DECLARE @mostchar INT
IF LEN(@Firstname1) > LEN(@Firstname2)
  @mostchar = LEN(@Firstname1)
ELSE
  @mostchar = LEN(@Firstname2)

SELECT Username
FROM Users
WHERE LEFT(Username, @mostchar) BETWEEN @Firstname1 AND @Firstname2 
ORDER BY Username

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-18 : 14:52:11

I think the 2nd option is better:

WHERE LOWER(LEFT(U.LastName, 1)) = LOWER(@FirstLetter)

also,

WHERE LOWER(LEFT(U.LastName, 2)) between

@StartFirstLetter+@EndFirstLetter and @StartSecondLetter+@EndSecondLetter

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-18 : 14:53:45

lol Sam! I am sniped!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources