Split multiple values in a cell to multiple rows - Oracle SQL The 2019 Stack Overflow Developer Survey Results Are InSplit function in oracle to comma separated values with automatic sequenceAdd a column with a default value to an existing table in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?How do I limit the number of rows returned by an Oracle query after ordering?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?SQL Query to concatenate column values from multiple rows in OracleGet top 1 row of each groupSQL select only rows with max value on a columnSQL query return data from multiple tables
How to notate time signature switching consistently every measure
How to deal with fear of taking dependencies
Are there incongruent pythagorean triangles with the same perimeter and same area?
How to support a colleague who finds meetings extremely tiring?
Which Sci-Fi work first showed weapon of galactic-scale mass destruction?
What is the closest word meaning "respect for time / mindful"
What is the accessibility of a package's `Private` context variables?
What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?
What did it mean to "align" a radio?
How to manage monthly salary
Interpreting the 2019 New York Reproductive Health Act?
Is "plugging out" electronic devices an American expression?
What is the most effective way of iterating a std::vector and why?
Are spiders unable to hurt humans, especially very small spiders?
How to type this arrow in math mode?
Am I thawing this London Broil safely?
Should I use my personal e-mail address, or my workplace one, when registering to external websites for work purposes?
Have you ever entered Singapore using a different passport or name?
Looking for Correct Greek Translation for Heraclitus
What do the Banks children have against barley water?
Loose spokes after only a few rides
Distributing a matrix
How to save as into a customized destination on macOS?
How are circuits which use complex ICs normally simulated?
Split multiple values in a cell to multiple rows - Oracle SQL
The 2019 Stack Overflow Developer Survey Results Are InSplit function in oracle to comma separated values with automatic sequenceAdd a column with a default value to an existing table in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?How do I limit the number of rows returned by an Oracle query after ordering?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?SQL Query to concatenate column values from multiple rows in OracleGet top 1 row of each groupSQL select only rows with max value on a columnSQL query return data from multiple tables
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a table:
table1
values
------------
x=new letter
------------
a=old letter
ba=older letter
xq=newer letter
------------
xf=new apple
xt=new orange
x3=new fruit
xtt=new seed
I have to separate the values in each cell to multiple rows.
The following is the output:
table2
code description
x new letter
a old letter
ba older letter
xq newer letter
xf new apple
xt new orange
x3 new fruit
xtt new seed
How can this be achieved?
sql oracle split
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
add a comment |
I have a table:
table1
values
------------
x=new letter
------------
a=old letter
ba=older letter
xq=newer letter
------------
xf=new apple
xt=new orange
x3=new fruit
xtt=new seed
I have to separate the values in each cell to multiple rows.
The following is the output:
table2
code description
x new letter
a old letter
ba older letter
xq newer letter
xf new apple
xt new orange
x3 new fruit
xtt new seed
How can this be achieved?
sql oracle split
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
Hint: useregexp_substr
– JohnHC
Mar 8 at 9:59
add a comment |
I have a table:
table1
values
------------
x=new letter
------------
a=old letter
ba=older letter
xq=newer letter
------------
xf=new apple
xt=new orange
x3=new fruit
xtt=new seed
I have to separate the values in each cell to multiple rows.
The following is the output:
table2
code description
x new letter
a old letter
ba older letter
xq newer letter
xf new apple
xt new orange
x3 new fruit
xtt new seed
How can this be achieved?
sql oracle split
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
I have a table:
table1
values
------------
x=new letter
------------
a=old letter
ba=older letter
xq=newer letter
------------
xf=new apple
xt=new orange
x3=new fruit
xtt=new seed
I have to separate the values in each cell to multiple rows.
The following is the output:
table2
code description
x new letter
a old letter
ba older letter
xq newer letter
xf new apple
xt new orange
x3 new fruit
xtt new seed
How can this be achieved?
sql oracle split
sql oracle split
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
edited Mar 8 at 10:21
a_horse_with_no_name
308k46470571
308k46470571
asked Mar 8 at 9:53
dangdang
59812044
asked Mar 8 at 9:53
dangdang
59812044
asked Mar 8 at 9:53
dangdang
59812044
59812044
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
Hint: useregexp_substr
– JohnHC
Mar 8 at 9:59
add a comment |
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
Hint: useregexp_substr
– JohnHC
Mar 8 at 9:59
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
Hint: use
regexp_substr– JohnHC
Mar 8 at 9:59
Hint: use
regexp_substr– JohnHC
Mar 8 at 9:59
add a comment |
2 Answers
2
active
oldest
votes
I would use regexp_replace() or regexp_substr():
select regexp_substr(str, '^[^=]+') as code,
regexp_substr(str, '[^=]+$') as value
Here is a db<>fiddle.
Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
add a comment |
try like below
SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter')
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')
FROM DUAL
so in you case
SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values)
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values)
FROM table1
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
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%2f55060669%2fsplit-multiple-values-in-a-cell-to-multiple-rows-oracle-sql%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
I would use regexp_replace() or regexp_substr():
select regexp_substr(str, '^[^=]+') as code,
regexp_substr(str, '[^=]+$') as value
Here is a db<>fiddle.
Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
add a comment |
I would use regexp_replace() or regexp_substr():
select regexp_substr(str, '^[^=]+') as code,
regexp_substr(str, '[^=]+$') as value
Here is a db<>fiddle.
Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
add a comment |
I would use regexp_replace() or regexp_substr():
select regexp_substr(str, '^[^=]+') as code,
regexp_substr(str, '[^=]+$') as value
Here is a db<>fiddle.
Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
I would use regexp_replace() or regexp_substr():
select regexp_substr(str, '^[^=]+') as code,
regexp_substr(str, '[^=]+$') as value
Here is a db<>fiddle.
Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
answered Mar 8 at 12:22
Gordon LinoffGordon Linoff
796k37318423
796k37318423
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
add a comment |
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
Would it also separate multiple values in a single cell to multiple rows?
– dang
Mar 9 at 14:15
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line
– dang
Mar 9 at 14:16
add a comment |
try like below
SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter')
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')
FROM DUAL
so in you case
SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values)
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values)
FROM table1
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
add a comment |
try like below
SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter')
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')
FROM DUAL
so in you case
SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values)
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values)
FROM table1
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
add a comment |
try like below
SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter')
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')
FROM DUAL
so in you case
SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values)
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values)
FROM table1
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
try like below
SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter')
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')
FROM DUAL
so in you case
SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values)
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values)
FROM table1
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
answered Mar 8 at 10:01
Zaynul Abadin TuhinZaynul Abadin Tuhin
19k31135
19k31135
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
add a comment |
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.
– dang
Mar 9 at 14:27
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%2f55060669%2fsplit-multiple-values-in-a-cell-to-multiple-rows-oracle-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
look at this link : stackoverflow.com/questions/28677070/…
– saravanatn
Mar 8 at 9:56
Hint: use
regexp_substr– JohnHC
Mar 8 at 9:59