1

I have several two character strings that include numbers like:

01, 02, 03 etc, However, there are others that look like this:

0P, as, JJ, F1, F2, XP, PK, TL, WQ

I am looking to exclude the ones in the second row with a query on redshift using their "not like" syntax. Tried several combinations and can't get it to work. Ideally, I am looking for a way to exclude any two character string that includes an alphabetic character.

user2022284
  • 413
  • 1
  • 9
  • 22

1 Answers1

1

Try with a regular expresion

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

  SELECT * 
  FROM YourTable 
  WHERE columnName REGEXP '[0-9]+';

Or if you want restrict to two characters

  SELECT * 
  FROM YourTable 
  WHERE columnName  REGEXP '[0-9][0-9]';

You say want to exclude any two character string that includes an alphabetic character.

This bring all where both chars are number.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • not working ( note that I am on Amazon Redshift). Will the syntax be different ? my code looks like : `where right( column_id, 2 ) REGEXP '[0-9][0-9]'` – user2022284 Feb 25 '16 at 16:32
  • I found another solution http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql – Juan Carlos Oropeza Feb 25 '16 at 16:39
  • Also you have [**REGEXP_REPLACE**](http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html) You can replace all number for `''` and see if you get a 0 length string,,, or all alpha and see if length is the same. But I dont have access to redshift to give you the right syntaxis – Juan Carlos Oropeza Feb 25 '16 at 16:41