CODEWITHSUNDEEP
sqloracle-databaseoracle11g
gayatri mali
gayatri mali

Reputation: 11

Using REGEXP_LIKE function in Oracle DB

I want to validate a string. The conditions are :

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

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

Upvotes: 1

Views: 7456

Answers (1)

Del
Del

Reputation: 1599

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)

Upvotes: 3

Related Questions