Oracle SQL Query to find if a field value has only alphanumeric values
One of my trainees called me and asked how to know if a field has only alphanumeric values. I suggested to check the ASCII values of the field ranging from A-Z, a-z and 0-9. However the field is not a single character field and hence usage of ASCII will not give the expected result. Sometime before I've posted about how to fetch data if a field value has at least only one or all numerals. This requirement is a mere extension of this previous post.
Query:
SELECT *
FROM TABLENAME
WHERE
LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','A')) = 0
This query will fetch all records which have only alphanumeric values. The right side operator '=0' needs to be changed to < LENGTH(FIELDTOBECHECKED) and = LENGTH(FIELDTOBECHECKED) to obtain various required results.
Query:
SELECT *
FROM TABLENAME
WHERE
LENGTH(TRANSLATE(FIELDTOBECHECKED,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','A')) = 0
This query will fetch all records which have only alphanumeric values. The right side operator '=0' needs to be changed to < LENGTH(FIELDTOBECHECKED) and = LENGTH(FIELDTOBECHECKED) to obtain various required results.
Hey Shyam,
ReplyDeleteHere is a simpler way if you use regular expressions.
SELECT *
FROM TABLENAME
WHERE regexp_like (FIELDTOBECHECKED,'^[A-Za-z0-9]+$')
Thanks Ram, thats a good suggestion
ReplyDelete