CODEWITHSUNDEEP
sqlregexdatabaseoracle-database
Susie
Susie

Reputation: 5148

Select alphabetic only strings of three characters length

I have a field in my table that is a varchar and contains alphabetic, numeric, and mixed alphanumeric values.

I would like to select only the values from that field which are 3 characters in length and alphabetic only.

I am using oracle.

Sample Data: 

AAA
BBB
12345
CCC25
DDDDD

The select statement should only return: 

AAA
BBB

I tried the following and it didn't work. This did not return anything. 

select name from MyTable where name like '[a-Z][a-Z][a-Z]';

Then I tried the following thinking it would return all 3-characters long results and it just returned everything: 

select name from MyTable where name like '%%%';

Upvotes: 2

Views: 2723

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

You can use the regexp_like function

SQL> with x as (
  2    select 'aaa' str from dual union all
  3    select 'bbb' from dual union all
  4    select '12345' from dual union all
  5    select 'CCC25' from dual union all
  6    select 'DDDDD' from dual
  7  )
  8  select *
  9    from x
 10   where regexp_like( str, '^[[:alpha:]]{3}$' );

STR
-----
aaa
bbb

Upvotes: 7

Related Questions