SQL regexp - replace dots between two numbers
I need to change this;
J S49-1:7.5-190 d.
to this;
J S49-1:7,5-190 d.
by replacing all dots with commas, but only between numbers.
oracle regexp-replace
edited Nov 14 '18 at 14:58
buræquete
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
add a comment |
I need to change this;
J S49-1:7.5-190 d.
to this;
J S49-1:7,5-190 d.
by replacing all dots with commas, but only between numbers.
oracle regexp-replace
edited Nov 14 '18 at 14:58
buræquete
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
add a comment |
I need to change this;
J S49-1:7.5-190 d.
to this;
J S49-1:7,5-190 d.
by replacing all dots with commas, but only between numbers.
oracle regexp-replace
edited Nov 14 '18 at 14:58
buræquete
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
I need to change this;
J S49-1:7.5-190 d.
to this;
J S49-1:7,5-190 d.
by replacing all dots with commas, but only between numbers.
oracle regexp-replace
oracle regexp-replace
edited Nov 14 '18 at 14:58
buræquete
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
edited Nov 14 '18 at 14:58
buræquete
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
edited Nov 14 '18 at 14:58
buræquete
5,30442049
edited Nov 14 '18 at 14:58
buræquete
5,30442049
edited Nov 14 '18 at 14:58
buræquete
5,30442049
5,30442049
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
asked Nov 14 '18 at 13:43
Jan PapotaJan Papota
273
273
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
With capture groups within REGEXP_REPLACE;
select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*d).(d.*)', '1,2') from dual;
will return;
J S49-1:7,5-190 d.
But this will not work if there are multiple occurrences of periods between digits.
In case there are more than one occurrence of period between digits
I was able to trigger multi replace with capture groups;
select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(d).(d)', '1,2', 1, 0) from dual;
will return;
J S49-1:7,557,8-190 d.
The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0
But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.
In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence
One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;
select REGEXP_REPLACE(
REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(d).(d)', '1,2', 1, 0),
'(d).(d)', '1,2', 1, 0) from dual;
will be the correct result;
J S49-1:7,5,8,7-190 d.
For more detail on REGEXP_REPLACE, check here
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
add a comment |
One way using REGEXP_SUBSTR:
select REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,1)||','||REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,2) from dual;
Here is with regexp_replce
select REGEXP_REPLACE('J S49-1:7.5-190 d','[.*]',',') from dual
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
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%2f53301654%2fsql-regexp-replace-dots-between-two-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
With capture groups within REGEXP_REPLACE;
select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*d).(d.*)', '1,2') from dual;
will return;
J S49-1:7,5-190 d.
But this will not work if there are multiple occurrences of periods between digits.
In case there are more than one occurrence of period between digits
I was able to trigger multi replace with capture groups;
select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(d).(d)', '1,2', 1, 0) from dual;
will return;
J S49-1:7,557,8-190 d.
The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0
But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.
In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence
One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;
select REGEXP_REPLACE(
REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(d).(d)', '1,2', 1, 0),
'(d).(d)', '1,2', 1, 0) from dual;
will be the correct result;
J S49-1:7,5,8,7-190 d.
For more detail on REGEXP_REPLACE, check here
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
add a comment |
With capture groups within REGEXP_REPLACE;
select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*d).(d.*)', '1,2') from dual;
will return;
J S49-1:7,5-190 d.
But this will not work if there are multiple occurrences of periods between digits.
In case there are more than one occurrence of period between digits
I was able to trigger multi replace with capture groups;
select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(d).(d)', '1,2', 1, 0) from dual;
will return;
J S49-1:7,557,8-190 d.
The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0
But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.
In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence
One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;
select REGEXP_REPLACE(
REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(d).(d)', '1,2', 1, 0),
'(d).(d)', '1,2', 1, 0) from dual;
will be the correct result;
J S49-1:7,5,8,7-190 d.
For more detail on REGEXP_REPLACE, check here
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
add a comment |
With capture groups within REGEXP_REPLACE;
select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*d).(d.*)', '1,2') from dual;
will return;
J S49-1:7,5-190 d.
But this will not work if there are multiple occurrences of periods between digits.
In case there are more than one occurrence of period between digits
I was able to trigger multi replace with capture groups;
select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(d).(d)', '1,2', 1, 0) from dual;
will return;
J S49-1:7,557,8-190 d.
The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0
But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.
In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence
One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;
select REGEXP_REPLACE(
REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(d).(d)', '1,2', 1, 0),
'(d).(d)', '1,2', 1, 0) from dual;
will be the correct result;
J S49-1:7,5,8,7-190 d.
For more detail on REGEXP_REPLACE, check here
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
With capture groups within REGEXP_REPLACE;
select REGEXP_REPLACE('J S49-1:7.5-190 d.', '(.*d).(d.*)', '1,2') from dual;
will return;
J S49-1:7,5-190 d.
But this will not work if there are multiple occurrences of periods between digits.
In case there are more than one occurrence of period between digits
I was able to trigger multi replace with capture groups;
select REGEXP_REPLACE('J S49-1:7.557.8-190 d.', '(d).(d)', '1,2', 1, 0) from dual;
will return;
J S49-1:7,557,8-190 d.
The last two parameters are; start_position and nth_appearance, to replace all occurrences, nth_appearance value must be 0
But still there is a problem when two capture groups share a digit, this is due to overlapping (same issue as this), and needs lookahead to resolve, but can't use it in oracle, hmmm.
In case there are more than one occurrence of period between digits, and same digit is shared between two occurrence
One solution would be to apply the secondary REGEXP_REPLACE I've shared twice, so any overlapping cases would be covered in the second run;
select REGEXP_REPLACE(
REGEXP_REPLACE('J S49-1:7.5.8.7-190 d.', '(d).(d)', '1,2', 1, 0),
'(d).(d)', '1,2', 1, 0) from dual;
will be the correct result;
J S49-1:7,5,8,7-190 d.
For more detail on REGEXP_REPLACE, check here
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
edited Nov 15 '18 at 8:10
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
answered Nov 14 '18 at 14:54
buræqueteburæquete
5,30442049
5,30442049
add a comment |
add a comment |
One way using REGEXP_SUBSTR:
select REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,1)||','||REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,2) from dual;
Here is with regexp_replce
select REGEXP_REPLACE('J S49-1:7.5-190 d','[.*]',',') from dual
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
add a comment |
One way using REGEXP_SUBSTR:
select REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,1)||','||REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,2) from dual;
Here is with regexp_replce
select REGEXP_REPLACE('J S49-1:7.5-190 d','[.*]',',') from dual
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
add a comment |
One way using REGEXP_SUBSTR:
select REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,1)||','||REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,2) from dual;
Here is with regexp_replce
select REGEXP_REPLACE('J S49-1:7.5-190 d','[.*]',',') from dual
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
One way using REGEXP_SUBSTR:
select REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,1)||','||REGEXP_SUBSTR('J S49-1:7.5-190 d','[^.]+',1,2) from dual;
Here is with regexp_replce
select REGEXP_REPLACE('J S49-1:7.5-190 d','[.*]',',') from dual
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
answered Nov 14 '18 at 14:43
kanagarajkanagaraj
35417
35417
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%2f53301654%2fsql-regexp-replace-dots-between-two-numbers%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
