question

KBSK avatar image
KBSK asked

SQL statement to retrieve names beginning with A or S or Z

Is there any statement like

select * from table where name like (A% , S%, Z%)

or is the below query only the solution

select * from table where name like 'A%' or name like 'S%' or name like 'Z%'
like
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
SELECT * FROM table WHERE name LIKE '[ASZ]%'

Edit: In case you have more questions related to LIKE and pattern matching, I just looked up the MSDN link to add here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - Prob the more flexible option...
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

Not exactly what you are looking for but may help...

 SELECT * 
 FROM [AdventureWorks].[Person].[Contact] c  
 WHERE LEFT (c.FirstName,1) in  ('G','M')
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Using a function like this is usually a performance killer because SQL is not able to use an index and you end up with a table or index scan, but LEFT is handled in the same way as LIKE 'C%'
4 Likes 4 ·
Erm... I have seen many many situations where a LEFT search will beat up a LIKE search, index or not - I think it's one of those 'horses for courses' things. Test, test, test again.
1 Like 1 ·
sadaf22 avatar image
sadaf22 answered
i want names from z and s for my new firm
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Try asking a new question rather than resurrecting an old and answered question...
0 Likes 0 ·
ruancra avatar image
ruancra answered
select * from table where name like 'A%' UNION select * from table where name like 'S%' UNION select * from table where name like 'Z%'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bhargav_kaklotara avatar image
bhargav_kaklotara answered
select * from table_name where left(name,1) between 'a' and 's' ;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.