I need to match a string to a pattern to validate the given string.
The given string could be like this 1234/5678.
I should validate the string in such a way that the first four and the last four characters will have to be numbers and they must be seperated by a slash.
How can I do this in SQL or PL/SQL?
I tried different functions such as REGEXP_LIKE, REGEXP_REPLACE,REGEXP_SUBSTR.
Can anyone please help me on this?
If this needs to be done in PL/SQL (e.g. you're validating user input, rather than data in a table), you can create a function to do the validation, e.g.:
DECLARE
v_str VARCHAR2(10);
FUNCTION validate_string (in_str VARCHAR2) RETURN BOOLEAN
IS
BEGIN
RETURN regexp_like(in_str, '\d{4}/\d{4}');
END validate_string;
PROCEDURE validation_output (in_str VARCHAR2)
IS
BEGIN
IF validate_string (in_str => in_str) THEN
dbms_output.put_line(in_str||': validated');
ELSE
dbms_output.put_line(in_str||': not validated');
END IF;
END validation_output;
BEGIN
v_str := '1234/5678';
validation_output (v_str);
v_str := '12/5678';
validation_output (v_str);
v_str := NULL;
validation_output (v_str);
END;
/
1234/5678: validated
12/5678: not validated
: not validated
if you are using oracle you can user regexp_like https://www.techonthenet.com/oracle/regexp_like.php if you are using mysql regexp or rlike https://dev.mysql.com/doc/refman/5.5/en/regexp.html for sqlserver IsMatch() https://github.com/zzzprojects/Eval-SQL.NET/wiki/SQL-Server-Regex-%7C-Use-regular-expression-to-search,-replace-and-split-text-in-SQL#sql-regex---ismatch
ORACLE
SELECT * FROM T WHERE COL REGEXP_LIKE REGULAREXP
MYSQL
SELECT * FROM T WHERE COL RLIKE REGULAREXP
SELECT * FROM T WHERE COL REGEXP REGULAREXP
sample table:
SELECT * FROM ns_98;
4321/4567
43/45
43898/4521
4388/4521
43885/45215
4388///4521
SELECT a
FROM ns_98
WHERE REGEXP_LIKE (a,'^[0-9]{4}/{1}[0-9]{4}$');
output:
4321/4567
4388/4521
