Using REGEXP_LIKE function in Oracle DB

Question

I want to validate a string. The conditions are :

It should be 9 chr long.
first character should be alphabet(in uppercase).
remainig chr should be between 0 and 9
No special character is allowed

I want to verify this using REGEXP_LIKE(). Please suggest.

OK. what have you attempted, and where are you stuck?

OldProgrammer
– OldProgrammer

2021-09-27 13:21:51 +00:00
Commented Sep 27, 2021 at 13:21 — OldProgrammer
– OldProgrammer, Commented Sep 27, 2021 at 13:21
regexp_like(string, '^[A-Z][0-9]')

gayatri mali
– gayatri mali

2021-09-27 13:35:43 +00:00
Commented Sep 27, 2021 at 13:35 — gayatri mali
– gayatri mali, Commented Sep 27, 2021 at 13:35

Del · Accepted Answer · 2021-09-27 14:59:28Z

3

You should be able to use the following REGEXP_LIKE to validate your inputs:

REGEXP_LIKE(td.text_value, '^[A-Z][0-9]{8}$')

Explaining the regular expression:

^ - Beginning of String
[A-Z] - A Capital Letter
[0-9]{8} - Exactly 8 digit characters
$ - End of String

Here is a query validating certain use cases:

WITH test_data (text_value) AS
(
  SELECT 'A12345678' FROM DUAL UNION ALL
  SELECT 'a12345678' FROM DUAL UNION ALL
  SELECT 'A1234567' FROM DUAL UNION ALL
  SELECT 'A123456789' FROM DUAL UNION ALL
  SELECT '$12345678' FROM DUAL
)
SELECT td.text_value, 
       CASE WHEN REGEXP_LIKE(td.text_value, '^[A-Z][0-9]{8}$') THEN 'Y' ELSE 'N' END AS VALID
FROM test_data td

Here is the link to Oracle's Regular Expression documentation (Link)

Additionally, here is a DBFiddle running the above query (Link)

edited Sep 27, 2021 at 14:59

answered Sep 27, 2021 at 14:53

Del

1,6311 gold badge10 silver badges18 bronze badges

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

1 Comment

gayatri mali Over a year ago

Thanks a lot for quick response...Its really helpful

Collectives™ on Stack Overflow

Using REGEXP_LIKE function in Oracle DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related