CODEWITHSUNDEEP
sql-serverregex
DasBooten
DasBooten

Reputation: 329

Regex for one column that has numbers and letters but not one or the other

I am attempting to search a column that contains alphanumeric ids in it but want to write a query that returns records with letters and numbers but not one or the other.

i.e Acceptable: jjk44kndkfndFF

i.e Not acceptable: 223232323232   or  aajnfdskDFdd

So far I have:

where  PATINDEX('%[^a-zA-Z0-9 ]%',columnInQuestion)

This returns all alphanumeric records. Any direction appreciated

Upvotes: 3

Views: 9532

Answers (2)

Kjetil S.
Kjetil S.

Reputation: 3777

It can be done with just one regexp:

^[a-zA-Z0-9]*([a-zA-Z][0-9]|[0-9][a-zA-Z])[a-zA-Z0-9]*$

It starts and ends with 0-x legal chars.

And somewhere there is a switch from a letter to a digit or from a digit to a letter.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

I think you need three predicates in the WHERE clause:

WHERE (columnInQuestion NOT LIKE '%[^a-zA-Z0-9]%') AND 
      (PATINDEX('%[a-zA-Z]%', columnInQuestion) <> 0) AND
      (PATINDEX('%[0-9]%', columnInQuestion) <> 0)
  • First predicate (columnInQuestion NOT LIKE '%[^a-zA-Z0-9]%') is true if columnInQuestion contains only alphanumeric characters
  • Second predicate (PATINDEX('%[a-zA-Z]%', columnInQuestion) <> 0) is true if there is at least one alphabetic character in columnInQuestion
  • Third predicate (PATINDEX('%[0-9]%', columnInQuestion) <> 0) is true if there is at least one numeric character in columnInQuestion

Upvotes: 4

Related Questions