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

Question

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

Answer 1

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.

Answer 2

You could make three calls to REGEXP_LIKE for each required assertion:

SELECT *
FROM yourTable
WHERE
    REGEXP_LIKE(col, '[A-Za-z]') AND       -- contains alphabet
    REGEXP_LIKE(col, '[0-9]')    AND       -- contains number
    NOT REGEXP_LIKE(col, '[^A-Za-z0-9]');  -- no special character

Note that here I am assuming that a "special" character is any non alphanumeric character.