Ryan Hill Ryan Hill - 10 days ago 7
SQL Question

Match the end half of an expersion to only letters SQL ORACLE

I've got a column of data in an SQL table and after a first pass it contains only data starting with 'BVH' and 'BVG'. I want it to only contain data that has 3 numeric characters after this and no more letters. I have tried

OUC not like 'BVG%[a-z]%' and OUC not like 'BVH%[a-z]%'


as I don't know if the letters are going to fall in the first, second or third (or multiple) positions following the first 3 letters. I also can't know exactly what letters are going to appear

Example data

BVH122
BVH174
BVH336
BVH123
BVH447
BVH447
BVH321
BVH573
BVG1NS
BVG1T2
BVH283
BVH172
BVG12T
BVG1T2

Answer

Is that what you need?

with dat as (select 'BVH122' a from dual
   union all select 'BVH174' a from dual
   union all select 'BVG1NS' a from dual
   union all select 'BVH172' a from dual
   union all select 'BVG12T' a from dual
)
select * from dat where regexp_like(a,'[A-Z]{3}[0-9]{3}');

So your query is:

select * from table where regexp_like(OUC,'BV[GH]{1}[0-9]{3}');