ORACLE SQL - REGEXP_LIKE Contains First Character As a Number and Second Character as an Alphabet

Question

I am trying to generate a query in Oracle where i can get records that has first character in String as 3 or 4 AND second character is an alphabet. The rest can be anything else.

Something like this

    SELECT COL1 FROM TABLE
WHERE REGEXP_LIKE (COL1, '3[A-Za-Z]')
OR REGEXP_LIKE (COL1, '4[A-Za-z]')

I Do get the output but for few records the data doesn't start with 3 or 4. Meaning it selects those records who have 3 and An alphabet together anywhere in the column.

ex: 10573T2 (10573T2). I have to query records that should start with either 3 or 4 and the next character should be a letter.

Any help would be great

Littlefoot · Accepted Answer · 2020-05-12 14:14:21Z

3

SQL> with test (col) as
  2    (select '10573T2' from dual union all
  3     select '3A1234F' from dual union all
  4     select '23XXX02' from dual union all
  5     select '4GABC23' from dual union all
  6     select '31234FX' from dual
  7    )
  8  select col
  9  from test
 10  where regexp_like(col, '(^3|^4)[[:alpha:]]');

COL
-------
3A1234F
4GABC23

SQL>

begins ^ with 3 or | 4
and is followed by a letter [[:alpha:]]

As of your ^ doubts: that character has two roles:

[^ ... ] - Non-Matching Character List: matches any character not in list ...
^ - Beginning of Line Anchor: match the subsequent expression only when it occurs at the beginning of a line.

edited May 12, 2020 at 14:14

answered May 12, 2020 at 12:50

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chirag Raj Over a year ago

Thank you very much. it works! I was in an assumption that '^' stands for NOT as in do not include characters following ^. For Example ^3 meaning don't include 3.

Littlefoot Over a year ago

You're welcome. I added some more info about the ^ character usage; have a look, please.

Gordon Linoff · Accepted Answer · 2020-05-12 19:22:57Z

0

You need to anchor the pattern at the beginning of the string:

REGEXP_LIKE(COL1, '^[34][A-Za-z]')

Here is a db<>fiddle

edited May 12, 2020 at 19:22

answered May 12, 2020 at 12:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Chirag Raj Over a year ago

Thanks! Although this throws an error that says it's an invalid range. The above comment explained by Littlefoot get's the result perfectly.

Gordon Linoff Over a year ago

@ChiragRaj . . .That was a typo in your original question that I missed. I fixed this answer.

Collectives™ on Stack Overflow

ORACLE SQL - REGEXP_LIKE Contains First Character As a Number and Second Character as an Alphabet

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related