Get substring with REGEXP_SUBSTR
0
I need to use regexp_substr, but I can't use it properly
I have column (l.id) with numbers, for example:
1234567891123!123 EXPECTED OUTPUT: 1234567891123
123456789112!123 EXPECTED OUTPUT: 123456789112
12345678911!123 EXPECTED OUTPUT: 12345678911
1234567891123!123 EXPECTED OUTPUT: 1234567891123
I want use regexp_substr before the exclamation mark (!)
SELECT REGEXP_SUBSTR(l.id,'[%!]',1,13) from l.table
is it ok ?
sql oracle regexp-replace
asked Nov 12 at 13:25
Georgy
667
add a comment |
0
I need to use regexp_substr, but I can't use it properly
I have column (l.id) with numbers, for example:
1234567891123!123 EXPECTED OUTPUT: 1234567891123
123456789112!123 EXPECTED OUTPUT: 123456789112
12345678911!123 EXPECTED OUTPUT: 12345678911
1234567891123!123 EXPECTED OUTPUT: 1234567891123
I want use regexp_substr before the exclamation mark (!)
SELECT REGEXP_SUBSTR(l.id,'[%!]',1,13) from l.table
is it ok ?
sql oracle regexp-replace
asked Nov 12 at 13:25
Georgy
667
Column data type?
– jarlh
Nov 12 at 13:27
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32
add a comment |
0
0
0
I need to use regexp_substr, but I can't use it properly
I have column (l.id) with numbers, for example:
1234567891123!123 EXPECTED OUTPUT: 1234567891123
123456789112!123 EXPECTED OUTPUT: 123456789112
12345678911!123 EXPECTED OUTPUT: 12345678911
1234567891123!123 EXPECTED OUTPUT: 1234567891123
I want use regexp_substr before the exclamation mark (!)
SELECT REGEXP_SUBSTR(l.id,'[%!]',1,13) from l.table
is it ok ?
sql oracle regexp-replace
asked Nov 12 at 13:25
Georgy
667
I need to use regexp_substr, but I can't use it properly
I have column (l.id) with numbers, for example:
1234567891123!123 EXPECTED OUTPUT: 1234567891123
123456789112!123 EXPECTED OUTPUT: 123456789112
12345678911!123 EXPECTED OUTPUT: 12345678911
1234567891123!123 EXPECTED OUTPUT: 1234567891123
I want use regexp_substr before the exclamation mark (!)
SELECT REGEXP_SUBSTR(l.id,'[%!]',1,13) from l.table
is it ok ?
sql oracle regexp-replace
sql oracle regexp-replace
asked Nov 12 at 13:25
Georgy
667
asked Nov 12 at 13:25
Georgy
667
edited Nov 13 at 6:56
asked Nov 12 at 13:25
Georgy
667
asked Nov 12 at 13:25
Georgy
667
asked Nov 12 at 13:25
Georgy
667
667
Column data type?
– jarlh
Nov 12 at 13:27
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32
add a comment |
Column data type?
– jarlh
Nov 12 at 13:27
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32
Column data type?
– jarlh
Nov 12 at 13:27
Column data type?
– jarlh
Nov 12 at 13:27
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32
add a comment |
5 Answers
5
active
oldest
votes
0
If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use
SELECT REGEXP_SUBSTR(l.id,'^d+')
assuming you have only numbers before !
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
add a comment |
3
You can try using INSTR() and substr()
DEMO
select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual
answered Nov 12 at 13:30
fa06
10.5k1917
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
add a comment |
3
You want to remove the exclamation mark and all following characters it seems. That is simply:
select regexp_replace(id, '!.*', '') from mytable;
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
1
@Georgy . . . It is really odd that you insist on usingregexp_substr()in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this usingregexp_substr()(which is quite feasible).
– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched fromREGEXP_SUBSTRtoREGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".
– Thorsten Kettner
Nov 12 at 17:45
add a comment |
0
If I understand correctly, this is the pattern that you want:
SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1)
FROM (SELECT '1234567891123!123' as id from dual) l
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
this can return everything after!if there is nothing preceding!in the string.
– Vamsi Prabhala
Nov 12 at 13:33
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
add a comment |
0
Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.
The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.
SQL> with tbl(id, str) as (
select 1, '1234567891123!123' from dual union all
select 2, '123456789112!123' from dual union all
select 3, '12345678911!123' from dual union all
select 4, '1234567891123!123' from dual union all
select 5, '!123' from dual union all
select 6, '123!' from dual union all
select 7, '' from dual union all
select 8, '12345' from dual
)
select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
from tbl
order by id;
ID REGEXP_SUBSTR(STR
---------- -----------------
1 1234567891123
2 123456789112
3 12345678911
4 1234567891123
5
6 123
7
8 12345
8 rows selected.
SQL>
answered Nov 13 at 14:30
Gary_W
6,70111129
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
});
}
});
draft saved
draft discarded
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%2f53263157%2fget-substring-with-regexp-substr%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use
SELECT REGEXP_SUBSTR(l.id,'^d+')
assuming you have only numbers before !
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
add a comment |
0
If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use
SELECT REGEXP_SUBSTR(l.id,'^d+')
assuming you have only numbers before !
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
add a comment |
0
0
0
If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use
SELECT REGEXP_SUBSTR(l.id,'^d+')
assuming you have only numbers before !
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use
SELECT REGEXP_SUBSTR(l.id,'^d+')
assuming you have only numbers before !
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
answered Nov 12 at 16:22
Wernfried Domscheit
23.9k42857
23.9k42857
add a comment |
add a comment |
3
You can try using INSTR() and substr()
DEMO
select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual
answered Nov 12 at 13:30
fa06
10.5k1917
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
add a comment |
3
You can try using INSTR() and substr()
DEMO
select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual
answered Nov 12 at 13:30
fa06
10.5k1917
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
add a comment |
3
3
3
You can try using INSTR() and substr()
DEMO
select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual
answered Nov 12 at 13:30
fa06
10.5k1917
You can try using INSTR() and substr()
DEMO
select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual
answered Nov 12 at 13:30
fa06
10.5k1917
answered Nov 12 at 13:30
fa06
10.5k1917
answered Nov 12 at 13:30
fa06
10.5k1917
answered Nov 12 at 13:30
fa06
10.5k1917
10.5k1917
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
add a comment |
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
1
1
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
It's a good solution, too.. thanks.
– Georgy
Nov 12 at 13:47
add a comment |
3
You want to remove the exclamation mark and all following characters it seems. That is simply:
select regexp_replace(id, '!.*', '') from mytable;
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
1
@Georgy . . . It is really odd that you insist on usingregexp_substr()in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this usingregexp_substr()(which is quite feasible).
– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched fromREGEXP_SUBSTRtoREGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".
– Thorsten Kettner
Nov 12 at 17:45
add a comment |
3
You want to remove the exclamation mark and all following characters it seems. That is simply:
select regexp_replace(id, '!.*', '') from mytable;
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
1
@Georgy . . . It is really odd that you insist on usingregexp_substr()in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this usingregexp_substr()(which is quite feasible).
– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched fromREGEXP_SUBSTRtoREGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".
– Thorsten Kettner
Nov 12 at 17:45
add a comment |
3
3
3
You want to remove the exclamation mark and all following characters it seems. That is simply:
select regexp_replace(id, '!.*', '') from mytable;
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
You want to remove the exclamation mark and all following characters it seems. That is simply:
select regexp_replace(id, '!.*', '') from mytable;
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
answered Nov 12 at 13:33
Thorsten Kettner
50.2k22542
50.2k22542
1
@Georgy . . . It is really odd that you insist on usingregexp_substr()in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this usingregexp_substr()(which is quite feasible).
– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched fromREGEXP_SUBSTRtoREGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".
– Thorsten Kettner
Nov 12 at 17:45
add a comment |
1
@Georgy . . . It is really odd that you insist on usingregexp_substr()in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this usingregexp_substr()(which is quite feasible).
– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched fromREGEXP_SUBSTRtoREGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".
– Thorsten Kettner
Nov 12 at 17:45
1
1
@Georgy . . . It is really odd that you insist on using
regexp_substr() in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this using regexp_substr() (which is quite feasible).– Gordon Linoff
Nov 12 at 14:14
@Georgy . . . It is really odd that you insist on using
regexp_substr() in the question and choose an answer that doesn't use it. Probably the best solution is fa06's answer that avoids regular expressions entirely -- but you seem to want to know how to do this using regexp_substr() (which is quite feasible).– Gordon Linoff
Nov 12 at 14:14
I'm with Gordon here. I must admit that I didn't even notice that I switched from
REGEXP_SUBSTR to REGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".– Thorsten Kettner
Nov 12 at 17:45
I'm with Gordon here. I must admit that I didn't even notice that I switched from
REGEXP_SUBSTR to REGEXP_REPLACE, as the latter is what came to mind when I saw you wanted to use a regular expression. As you are satisfied with my answer, it shows you had better asked more generally: "How do I get the substring before the exclamation mark. Here is my take with REGEXP_SUBSTR, but it doesn't work and gives me the following result ... instead of the desired ...".– Thorsten Kettner
Nov 12 at 17:45
add a comment |
0
If I understand correctly, this is the pattern that you want:
SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1)
FROM (SELECT '1234567891123!123' as id from dual) l
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
this can return everything after!if there is nothing preceding!in the string.
– Vamsi Prabhala
Nov 12 at 13:33
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
add a comment |
0
If I understand correctly, this is the pattern that you want:
SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1)
FROM (SELECT '1234567891123!123' as id from dual) l
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
this can return everything after!if there is nothing preceding!in the string.
– Vamsi Prabhala
Nov 12 at 13:33
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
add a comment |
0
0
0
If I understand correctly, this is the pattern that you want:
SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1)
FROM (SELECT '1234567891123!123' as id from dual) l
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
If I understand correctly, this is the pattern that you want:
SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1)
FROM (SELECT '1234567891123!123' as id from dual) l
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
edited Nov 12 at 13:37
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
answered Nov 12 at 13:28
Gordon Linoff
755k35290398
755k35290398
this can return everything after!if there is nothing preceding!in the string.
– Vamsi Prabhala
Nov 12 at 13:33
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
add a comment |
this can return everything after!if there is nothing preceding!in the string.
– Vamsi Prabhala
Nov 12 at 13:33
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
this can return everything after
! if there is nothing preceding ! in the string.– Vamsi Prabhala
Nov 12 at 13:33
this can return everything after
! if there is nothing preceding ! in the string.– Vamsi Prabhala
Nov 12 at 13:33
1
1
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
When someone notices a mistake in your answer, and you correct it based on that comment, it would help future readers to acknowledge it. Otherwise the comment may seem out of place.
– mathguy
Nov 12 at 13:48
add a comment |
0
Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.
The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.
SQL> with tbl(id, str) as (
select 1, '1234567891123!123' from dual union all
select 2, '123456789112!123' from dual union all
select 3, '12345678911!123' from dual union all
select 4, '1234567891123!123' from dual union all
select 5, '!123' from dual union all
select 6, '123!' from dual union all
select 7, '' from dual union all
select 8, '12345' from dual
)
select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
from tbl
order by id;
ID REGEXP_SUBSTR(STR
---------- -----------------
1 1234567891123
2 123456789112
3 12345678911
4 1234567891123
5
6 123
7
8 12345
8 rows selected.
SQL>
answered Nov 13 at 14:30
Gary_W
6,70111129
add a comment |
0
Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.
The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.
SQL> with tbl(id, str) as (
select 1, '1234567891123!123' from dual union all
select 2, '123456789112!123' from dual union all
select 3, '12345678911!123' from dual union all
select 4, '1234567891123!123' from dual union all
select 5, '!123' from dual union all
select 6, '123!' from dual union all
select 7, '' from dual union all
select 8, '12345' from dual
)
select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
from tbl
order by id;
ID REGEXP_SUBSTR(STR
---------- -----------------
1 1234567891123
2 123456789112
3 12345678911
4 1234567891123
5
6 123
7
8 12345
8 rows selected.
SQL>
answered Nov 13 at 14:30
Gary_W
6,70111129
add a comment |
0
0
0
Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.
The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.
SQL> with tbl(id, str) as (
select 1, '1234567891123!123' from dual union all
select 2, '123456789112!123' from dual union all
select 3, '12345678911!123' from dual union all
select 4, '1234567891123!123' from dual union all
select 5, '!123' from dual union all
select 6, '123!' from dual union all
select 7, '' from dual union all
select 8, '12345' from dual
)
select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
from tbl
order by id;
ID REGEXP_SUBSTR(STR
---------- -----------------
1 1234567891123
2 123456789112
3 12345678911
4 1234567891123
5
6 123
7
8 12345
8 rows selected.
SQL>
answered Nov 13 at 14:30
Gary_W
6,70111129
Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.
The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.
SQL> with tbl(id, str) as (
select 1, '1234567891123!123' from dual union all
select 2, '123456789112!123' from dual union all
select 3, '12345678911!123' from dual union all
select 4, '1234567891123!123' from dual union all
select 5, '!123' from dual union all
select 6, '123!' from dual union all
select 7, '' from dual union all
select 8, '12345' from dual
)
select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
from tbl
order by id;
ID REGEXP_SUBSTR(STR
---------- -----------------
1 1234567891123
2 123456789112
3 12345678911
4 1234567891123
5
6 123
7
8 12345
8 rows selected.
SQL>
answered Nov 13 at 14:30
Gary_W
6,70111129
answered Nov 13 at 14:30
Gary_W
6,70111129
answered Nov 13 at 14:30
Gary_W
6,70111129
answered Nov 13 at 14:30
Gary_W
6,70111129
6,70111129
add a comment |
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53263157%2fget-substring-with-regexp-substr%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
Column data type?
– jarlh
Nov 12 at 13:27
I suppose that with "I want use regexp_substr before the exclamation mark" you mean "I want use regexp_substr in oder to get the substring before the exclamation mark"?
– Thorsten Kettner
Nov 12 at 13:37
By "EXCEPTED" do you mean "EXPECTED"?
– mathguy
Nov 12 at 13:45
@mathguy, yep.. edited.
– Georgy
Nov 12 at 13:48
FYI - The percent sign matches all characters in SQL, but not in regular expressions.
– Gary_W
Nov 13 at 14:32