Skip to Main Content
  • Questions

  • Search for string containing letters

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, David.

Asked: November 28, 2016 - 2:38 pm UTC

Last updated: November 28, 2016 - 4:02 pm UTC

Version: 11.0.2

Viewed 1000+ times

You Asked

Hi,

I'm trying to create a SQL statement with a condition in the WHERE clause that will deliver all results that start with 3 letters.

The column type is varchar, and all entries either look like 'ABC123' or 'A12345'.

I'm not looking to return results that start with a specific letter (as in "where column = 'A%'), but for results that start with any 3 letters, to return entries like 'ABC123' or 'DEF123', but not 'A12345'.

How can this be done?

Regards,

David

and Chris said...

You could go for regular expressions:

create table t (
  x varchar2(10)
);

insert into t values ('ABC123');
insert into t values ('DEF456');
insert into t values ('ABCDEF');
insert into t values ('123456');
insert into t values ('123ABC');
insert into t values ('A12345');
insert into t values ('12');
insert into t values ('AB');

commit;

select * from t
where  regexp_like ( x, '^[[:alpha:]]{3}');

X       
ABC123  
DEF456  
ABCDEF


Or standard string manipulation:

select *
from   t
where  translate(
  upper( substr(x, 1, 3) ), 
 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
 '##########################'
) = '###';

X       
ABC123  
DEF456  
ABCDEF

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

David, December 01, 2016 - 10:15 am UTC

Hi Chris,

thanks for the quick response, it was very helpful.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.