Mixing
String Pattern Matching in Sql / Pl/sql
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?
sql regex plsql
asked Nov 20 '18 at 9:18
Anji007Anji007
178
add a comment |
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?
sql regex plsql
asked Nov 20 '18 at 9:18
Anji007Anji007
178
1
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00
add a comment |
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?
sql regex plsql
asked Nov 20 '18 at 9:18
Anji007Anji007
178
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?
sql regex plsql
sql regex plsql
asked Nov 20 '18 at 9:18
Anji007Anji007
178
asked Nov 20 '18 at 9:18
Anji007Anji007
178
asked Nov 20 '18 at 9:18
Anji007Anji007
178
asked Nov 20 '18 at 9:18
Anji007Anji007
178
asked Nov 20 '18 at 9:18
Anji007Anji007
178
178
1
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00
add a comment |
1
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00
1
1
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00
add a comment |
3 Answers
3
active
oldest
votes
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
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
add a comment |
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
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
add a comment |
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
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389731%2fstring-pattern-matching-in-sql-pl-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
add a comment |
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
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
add a comment |
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
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
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
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
edited Nov 20 '18 at 9:33
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
answered Nov 20 '18 at 9:28
David MarabottiniDavid Marabottini
1707
1707
add a comment |
add a comment |
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
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
add a comment |
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
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
add a comment |
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
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
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
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
answered Nov 20 '18 at 10:42
BoneistBoneist
18.1k11028
18.1k11028
add a comment |
add a comment |
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
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
add a comment |
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
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
add a comment |
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
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
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
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
edited Nov 20 '18 at 11:09
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
answered Nov 20 '18 at 10:35
nikhil sugandhnikhil sugandh
1,2562719
1,2562719
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53389731%2fstring-pattern-matching-in-sql-pl-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
So what have you tried
– a_horse_with_no_name
Nov 20 '18 at 9:20
how are you receiving the string? Is it in a column in a table, or will it be passed around in PL/SQL as a parameter?
– Boneist
Nov 20 '18 at 10:00