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

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

Answer

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.