Header Ads

  • Recent Posts

    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.

    2 comments:

    1. Hey Shyam,
      Here is a simpler way if you use regular expressions.

      SELECT *
      FROM TABLENAME
      WHERE regexp_like (FIELDTOBECHECKED,'^[A-Za-z0-9]+$')

      ReplyDelete
    2. Thanks Ram, thats a good suggestion

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad