Oracle SQL REG EXP

Jamil Muammar
Jamil Muammar used Ask the Experts™
on
Dear Experts,

 How do I in Oracle SQL check if the field contain text with length 18 & contain [a..z,A..Z,0..9] chars only

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
create table test(id number, str varchar2(50));

insert into test values(1,  '123456789123456789');
insert into test values(2,  'abcdefghi123456789');
insert into test values(3,  'aA34567891234567zZ');
insert into test values(4,  '12345678912345678910');
insert into test values(5,  '.?3456789123456789');
insert into test values(6,  '_$3456789123456789');
insert into test values(7,  'xx123456789123456789');
insert into test values(8,  'AA123456789123456789');
insert into test values(9,  'foo 123456789123456789');
insert into test values(10, '  3456789123456789');

Open in new window


select a.str,
       length(a.str)
  from test a
 where regexp_like(a.str, '[[:alnum:]]{18}')
   and length(a.str) = 18;

select a.str,
       length(a.str),
       regexp_instr(a.str, '[[:alnum:]]{18}')
  from test a
 where length(a.str) = 18;

Open in new window


http://sqlfiddle.com/#!4/30060/11
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Slight modification that removes the length check:
select a.str,
       length(a.str)
  from test a
 where regexp_like(a.str, '^[[:alnum:]]{18}$');
   

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial