oracle-databaseregexp-like

oracle regular expression to check string contains both alphabet and number and does not contain special characters

How to create an Oracle regular expression to check whether the given string contains both number and alphabet and does not contain special character. For example,

if the string is like 'kjds327' it must return true

if the string is 'dkfsdsf' or '132564' or 'asjv@3#34342fd' it must return false

Solution

  • You can use REGEXP_LIKE as follows:

    select * from your_table
 where regexp_like(your_column,'([a-zA-Z][0-9]+)|([0-9][a-zA-Z]+)')
   and not regexp_like(your_column,'[^a-zA-Z0-9]')

    db<>fiddle

    You can use CASE statement with this regexp in SELECT clause if you want true and false as a result.