Using SELECT to obtain only fields with alphanumeric data (and certain punctuation) in SQL Server

Question

I am trying to filter some SQL server data and require results with the following conditions:

• where the field has alphanumeric characters, case insensitive
• where the field has certain punctuation (apostrophe and hyphen)
• where the field has no spaces

Is there an efficient way to do this using CHAR in SQL Server or does anybody have a better solution?

Answer 1

This uses a double negative to filter only to the desired range of characters

Any character outside the desired range gives true from LIKE. If the string consists only of character in the desired range, LIKE gives false. Then another NOT

WHERE
   SomeCol NOT LIKE '%[^a-z0-9-'']%'

Note: I used single quote here

By default, SQL Server is case insensitive. Add a COLLATE clause if needed

   SomeCol COLLATE Latin1_General_CI_AS NOT LIKE '%[^a-z0-9-']%'

or change the range

   SomeCol NOT LIKE '%[^a-zA-Z0-9-']%'

or, if you want to include ä = a

   SomeCol COLLATE Latin1_General_CI_AI NOT LIKE '%[^a-z0-9-']%'