How to remove all characters except 'E' in oracleHow to validate an email address in JavaScript?How can I prevent SQL injection in PHP?How to validate an email address using a regular expression?Get list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?How do I make the first letter of a string uppercase in JavaScript?How to replace all occurrences of a string in JavaScriptRegEx match open tags except XHTML self-contained tagsHow do I UPDATE from a SELECT in SQL Server?How do I remove all non alphanumeric characters from a string except dash?
Store Credit Card Information in Password Manager?
How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?
Creepy dinosaur pc game identification
Yosemite Fire Rings - What to Expect?
Unexpected behavior of the procedure `Area` on the object 'Polygon'
What are the advantages of simplicial model categories over non-simplicial ones?
Does IPv6 have similar concept of network mask?
Has any country ever had 2 former presidents in jail simultaneously?
Can I say "fingers" when referring to toes?
Can I still be respawned if I die by falling off the map?
What is the highest possible scrabble score for placing a single tile
Are Captain Marvel's powers affected by Thanos' actions in Infinity War
Do we have to expect a queue for the shuttle from Watford Junction to Harry Potter Studio?
Why should universal income be universal?
Using substitution ciphers to generate new alphabets in a novel
Why is so much work done on numerical verification of the Riemann Hypothesis?
Why is the "ls" command showing permissions of files in a FAT32 partition?
putting logo on same line but after title, latex
How can I write humor as character trait?
Do the primes contain an infinite almost arithmetic progression?
Mixing PEX brands
Angel of Condemnation - Exile creature with second ability
How to create table with 2D function values?
Pre-mixing cryogenic fuels and using only one fuel tank
How to remove all characters except 'E' in oracle
How to validate an email address in JavaScript?How can I prevent SQL injection in PHP?How to validate an email address using a regular expression?Get list of all tables in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?How do I make the first letter of a string uppercase in JavaScript?How to replace all occurrences of a string in JavaScriptRegEx match open tags except XHTML self-contained tagsHow do I UPDATE from a SELECT in SQL Server?How do I remove all non alphanumeric characters from a string except dash?
I have strig like 'AA_0331L_02317_R5_P' and i want remove all characters except 'E' from second part after splitting with _ character, so here it is 0331N should become 0331 and if it comes like 0331E , then it should become 0331E .ie simply if i have i string like AA_0331N_02317_R5_P , then i want to be AA_0331_02317_R5_P and if i have a AA_0331E_02317_R5_P ,then it should be AA_0331E_02317_R5_P. I did like as shown below without any luck
SELECT REGEXP_REPLACE(REGEXP_SUBSTR( 'AA_0331L_02317_R5_P' , '[^_]+', 1, 2 ), '[^0-9]', '')
FROM dual
sql regex oracle
edited yesterday
David Faber
10.5k22236
asked yesterday
peterpeter
3,154185282
add a comment |
I have strig like 'AA_0331L_02317_R5_P' and i want remove all characters except 'E' from second part after splitting with _ character, so here it is 0331N should become 0331 and if it comes like 0331E , then it should become 0331E .ie simply if i have i string like AA_0331N_02317_R5_P , then i want to be AA_0331_02317_R5_P and if i have a AA_0331E_02317_R5_P ,then it should be AA_0331E_02317_R5_P. I did like as shown below without any luck
SELECT REGEXP_REPLACE(REGEXP_SUBSTR( 'AA_0331L_02317_R5_P' , '[^_]+', 1, 2 ), '[^0-9]', '')
FROM dual
sql regex oracle
edited yesterday
David Faber
10.5k22236
asked yesterday
peterpeter
3,154185282
add a comment |
I have strig like 'AA_0331L_02317_R5_P' and i want remove all characters except 'E' from second part after splitting with _ character, so here it is 0331N should become 0331 and if it comes like 0331E , then it should become 0331E .ie simply if i have i string like AA_0331N_02317_R5_P , then i want to be AA_0331_02317_R5_P and if i have a AA_0331E_02317_R5_P ,then it should be AA_0331E_02317_R5_P. I did like as shown below without any luck
SELECT REGEXP_REPLACE(REGEXP_SUBSTR( 'AA_0331L_02317_R5_P' , '[^_]+', 1, 2 ), '[^0-9]', '')
FROM dual
sql regex oracle
edited yesterday
David Faber
10.5k22236
asked yesterday
peterpeter
3,154185282
I have strig like 'AA_0331L_02317_R5_P' and i want remove all characters except 'E' from second part after splitting with _ character, so here it is 0331N should become 0331 and if it comes like 0331E , then it should become 0331E .ie simply if i have i string like AA_0331N_02317_R5_P , then i want to be AA_0331_02317_R5_P and if i have a AA_0331E_02317_R5_P ,then it should be AA_0331E_02317_R5_P. I did like as shown below without any luck
SELECT REGEXP_REPLACE(REGEXP_SUBSTR( 'AA_0331L_02317_R5_P' , '[^_]+', 1, 2 ), '[^0-9]', '')
FROM dual
sql regex oracle
sql regex oracle
edited yesterday
David Faber
10.5k22236
asked yesterday
peterpeter
3,154185282
edited yesterday
David Faber
10.5k22236
asked yesterday
peterpeter
3,154185282
edited yesterday
David Faber
10.5k22236
edited yesterday
David Faber
10.5k22236
edited yesterday
David Faber
10.5k22236
10.5k22236
asked yesterday
peterpeter
3,154185282
asked yesterday
peterpeter
3,154185282
asked yesterday
peterpeter
3,154185282
3,154185282
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
with s as (
select 'AA_0331L_02317_R5_P' str from dual union all
select 'AA_0331E_02317_R5_P' str from dual)
select str,
regexp_replace(regexp_substr(str, '[^_]+_[^_]+'), '[^E]$') || regexp_replace(str, '[^_]+_[^_]+', '', 1, 1) new_str
from s;
STR NEW_STR
------------------------------ ------------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
akk0rd87akk0rd87
2326
add a comment |
You might try something like the following -- keeping in mind that REGEXP_REPLACE() will return the original string if nothing is actually replaced. Here I'm using backreferences (if Oracle regexes had lookahead I could have omitted the 2nd capturing group and backreference):
WITH mytable AS (
SELECT 'AA_0331L_02317_R5_P' AS myvalue
FROM dual
UNION ALL
SELECT 'AA_0331N_02317_R5_P'
FROM dual
UNION ALL
SELECT 'AA_0331E_02317_R5_P'
FROM dual
)
SELECT myvalue
, REGEXP_REPLACE(myvalue, '^([^_]+_[^_]+)[^E](_)', '12') mynewvalue
FROM mytable;
MYVALUE MYNEWVALUE
------------------------- -------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331N_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
David FaberDavid Faber
10.5k22236
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%2f55280556%2fhow-to-remove-all-characters-except-e-in-oracle%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 s as (
select 'AA_0331L_02317_R5_P' str from dual union all
select 'AA_0331E_02317_R5_P' str from dual)
select str,
regexp_replace(regexp_substr(str, '[^_]+_[^_]+'), '[^E]$') || regexp_replace(str, '[^_]+_[^_]+', '', 1, 1) new_str
from s;
STR NEW_STR
------------------------------ ------------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
akk0rd87akk0rd87
2326
add a comment |
with s as (
select 'AA_0331L_02317_R5_P' str from dual union all
select 'AA_0331E_02317_R5_P' str from dual)
select str,
regexp_replace(regexp_substr(str, '[^_]+_[^_]+'), '[^E]$') || regexp_replace(str, '[^_]+_[^_]+', '', 1, 1) new_str
from s;
STR NEW_STR
------------------------------ ------------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
akk0rd87akk0rd87
2326
add a comment |
with s as (
select 'AA_0331L_02317_R5_P' str from dual union all
select 'AA_0331E_02317_R5_P' str from dual)
select str,
regexp_replace(regexp_substr(str, '[^_]+_[^_]+'), '[^E]$') || regexp_replace(str, '[^_]+_[^_]+', '', 1, 1) new_str
from s;
STR NEW_STR
------------------------------ ------------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
akk0rd87akk0rd87
2326
with s as (
select 'AA_0331L_02317_R5_P' str from dual union all
select 'AA_0331E_02317_R5_P' str from dual)
select str,
regexp_replace(regexp_substr(str, '[^_]+_[^_]+'), '[^E]$') || regexp_replace(str, '[^_]+_[^_]+', '', 1, 1) new_str
from s;
STR NEW_STR
------------------------------ ------------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
akk0rd87akk0rd87
2326
answered yesterday
akk0rd87akk0rd87
2326
answered yesterday
akk0rd87akk0rd87
2326
answered yesterday
akk0rd87akk0rd87
2326
2326
add a comment |
add a comment |
You might try something like the following -- keeping in mind that REGEXP_REPLACE() will return the original string if nothing is actually replaced. Here I'm using backreferences (if Oracle regexes had lookahead I could have omitted the 2nd capturing group and backreference):
WITH mytable AS (
SELECT 'AA_0331L_02317_R5_P' AS myvalue
FROM dual
UNION ALL
SELECT 'AA_0331N_02317_R5_P'
FROM dual
UNION ALL
SELECT 'AA_0331E_02317_R5_P'
FROM dual
)
SELECT myvalue
, REGEXP_REPLACE(myvalue, '^([^_]+_[^_]+)[^E](_)', '12') mynewvalue
FROM mytable;
MYVALUE MYNEWVALUE
------------------------- -------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331N_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
David FaberDavid Faber
10.5k22236
add a comment |
You might try something like the following -- keeping in mind that REGEXP_REPLACE() will return the original string if nothing is actually replaced. Here I'm using backreferences (if Oracle regexes had lookahead I could have omitted the 2nd capturing group and backreference):
WITH mytable AS (
SELECT 'AA_0331L_02317_R5_P' AS myvalue
FROM dual
UNION ALL
SELECT 'AA_0331N_02317_R5_P'
FROM dual
UNION ALL
SELECT 'AA_0331E_02317_R5_P'
FROM dual
)
SELECT myvalue
, REGEXP_REPLACE(myvalue, '^([^_]+_[^_]+)[^E](_)', '12') mynewvalue
FROM mytable;
MYVALUE MYNEWVALUE
------------------------- -------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331N_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
David FaberDavid Faber
10.5k22236
add a comment |
You might try something like the following -- keeping in mind that REGEXP_REPLACE() will return the original string if nothing is actually replaced. Here I'm using backreferences (if Oracle regexes had lookahead I could have omitted the 2nd capturing group and backreference):
WITH mytable AS (
SELECT 'AA_0331L_02317_R5_P' AS myvalue
FROM dual
UNION ALL
SELECT 'AA_0331N_02317_R5_P'
FROM dual
UNION ALL
SELECT 'AA_0331E_02317_R5_P'
FROM dual
)
SELECT myvalue
, REGEXP_REPLACE(myvalue, '^([^_]+_[^_]+)[^E](_)', '12') mynewvalue
FROM mytable;
MYVALUE MYNEWVALUE
------------------------- -------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331N_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
David FaberDavid Faber
10.5k22236
You might try something like the following -- keeping in mind that REGEXP_REPLACE() will return the original string if nothing is actually replaced. Here I'm using backreferences (if Oracle regexes had lookahead I could have omitted the 2nd capturing group and backreference):
WITH mytable AS (
SELECT 'AA_0331L_02317_R5_P' AS myvalue
FROM dual
UNION ALL
SELECT 'AA_0331N_02317_R5_P'
FROM dual
UNION ALL
SELECT 'AA_0331E_02317_R5_P'
FROM dual
)
SELECT myvalue
, REGEXP_REPLACE(myvalue, '^([^_]+_[^_]+)[^E](_)', '12') mynewvalue
FROM mytable;
MYVALUE MYNEWVALUE
------------------------- -------------------------
AA_0331L_02317_R5_P AA_0331_02317_R5_P
AA_0331N_02317_R5_P AA_0331_02317_R5_P
AA_0331E_02317_R5_P AA_0331E_02317_R5_P
answered yesterday
David FaberDavid Faber
10.5k22236
edited yesterday
answered yesterday
David FaberDavid Faber
10.5k22236
answered yesterday
David FaberDavid Faber
10.5k22236
answered yesterday
David FaberDavid Faber
10.5k22236
10.5k22236
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%2f55280556%2fhow-to-remove-all-characters-except-e-in-oracle%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
