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

Question

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

Answer 1

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