All geek questions in one place

Using SELECT to retrieve only fields with alphanumeric data (and specific punctuation) in SQL Server

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

  • where the field has alphanumeric characters, case insensitive
  • where the field has a 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 anyone have a better solution?

+6
source share
1 answer

This uses a double negative filter to filter only to the desired character range.

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

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

Note. I used a single quote here

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

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

or change 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-']%'
+14
source

Source: https://habr.com/ru/post/905201/

More articles:


All Articles