Sql Oracle query - field contains special characters or alpha

user_10356126 0
Viewed 4019

need to see if a taxid field contains letters or any special characters:. *,!,?,@,#,$,&,+,(,),/

How can I write this SQL?

select oracle sql
2 Answers

Oracle has regexp_like:

select * from tablename where regexp_like(columnname, '[*!?@#$&+()/]');
user_4583680

Here is the best way to display all the taxid's that are NOT entirely composed of digits:

select taxid
from   your_table
where  translate(taxid, '.0123456789', '.') is not null

TRANSLATE will "translate" (replace) each period in the input with a period in the output. Since the other characters in the second argument do not have a corresponding character in the third argument, they will simply be deleted. If the result of this operation is not null, then the taxid contains at least one character that is not a digit. If taxid is all digits, the result of the operation is null. Note that the period character used here is needed, due to an oddity in Oracle's definition of TRANSLATE: if any of its arguments is null, then so is its return value. That doesn't make a lot of sense, but we must work with the functions as Oracle defined them.

Admin1
Related