CODEWITHSUNDEEP
sqlteradata
gbtimmon
gbtimmon

Reputation: 4332

SQL where 'does not contain Numerics', is it possible?

I have a table full of rows, each row has a status and a sequence id (and a bunch of super secret stuff which isn't important here). The status is either a string indicating the current status of the record, or an id number indicating it is a child to a parent with that sequence number, who will contain the status of the tree. Basically our table contains Trees where the parents hold status information and all the children contain upward pointers. (It's pretty bad designed I know, but it is legacy and not worth changing quite yet.)

First what I need is a list of all of the statuses for all of our families, and their sequence ids. So I would need something like

SELECT UNIQUE status, seq_id FROM top_secret_database.government_secrets_table 
WHERE status <does not contain numerics>

does anyone know a way to accomplish this in sql?

I can do 

SELECT UNIQUE status, seq_id FROM top_secret_database.government_secrets_table 
WHERE status NOT IN (
    SELECT UNIQUE seq_id FROM top_secret_database.government_secrets_table
)

But at this point I'm just curious if there is any way to get any regex like 'not numerics' in sql that anyone knows of because that would be useful done the line. Any suggestions?

Upvotes: 0

Views: 3453

Answers (3)

Rob Paller
Rob Paller

Reputation: 7786

If you know the acceptable range of numeric status ids you could use the Teradata native CHAR2HEXINT() function to determine if the first character of the column is a character or numeric.

SELECT CHAR2HEXINT('A')
     , CHAR2HEXINT('Z')
     , CHAR2HEXINT('0')
     , CHAR2HEXINT('1')
     , CHAR2HEXINT('9')
;

Upvotes: 2

Bort
Bort

Reputation: 7618

If the status is either a string containing no numbers, or a fully numeric Id, you can use (at least in TSQL) the ISNUMERIC function to filter.

Upvotes: 0

Marc B
Marc B

Reputation: 360702

You don't say WHICH sql server you're using, so I'm going to assume MySQL:

SELECT ...
WHERE status NOT REGEXP '[0-9]'

Upvotes: 0

Related Questions