Search code examples
sql-servert-sql

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

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?

Solution

  • 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-']%'