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
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]')
You can use CASE statement with this regexp in SELECT clause if you want true and false as a result.
REGEXP_LIKE(col, '[a-z][0-9]|[0-9][a-z]', 'i'), using a simpler pattern in case insensitive mode.
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.
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