CODEWITHSUNDEEP
sqloracle-databaseselect
Cheryl L Hubert
Cheryl L Hubert

Reputation: 63

Sql Oracle query - field contains special characters or alpha

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

How can I write this SQL?

Upvotes: 3

Views: 9495

Answers (2)

user5683823
user5683823

Reputation:

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.

Upvotes: 0

JOTN
JOTN

Reputation: 6317

Oracle has regexp_like:

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

Upvotes: 5

Related Questions