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

user_2905758 0
Viewed 3917

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

regexp-like oracle
3 Answers

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.

user_115656290

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.

user_18632290

You only need a single regular expression for this task.

A moment's reflection will reveal that a string that must contain at least one letter and at least one digit, and contains ONLY letters and digits, must contain either a letter followed immediately by a digit, or a digit followed immediately by a letter. I won't spend much time on this, since it's a matter of basic logic (not of Oracle SQL programming).

With this insight, the solution becomes clear. You need something like this (using POSIX bracket expressions). Don't forget the anchors ^ and $ to make sure that the entire string is matched, not just a part of it.

with
  sample_inputs(string) as (
    select 'kjds327'        from dual union all
    select 'dkfsdsf'        from dual union all
    select '132564'         from dual union all
    select 'asjv@3#34342fd' from dual union all
    select null             from dual union all
    select '8B'             from dual
  )
select string,
       case when regexp_like(string, 
  '^[[:alnum:]]*(([[:alpha:]][[:digit:]])|([[:digit:]][[:alpha:]]))[[:alnum:]]*$')
         then 'true' else 'false' end as test_result
from   sample_inputs
;

STRING         TEST_RESULT
-------------- -----------
kjds327        true 
dkfsdsf        false
132564         false
asjv@3#34342fd false
               false
8B             true
Admin1
Related