Mixing
Is there a way to seperate 2 values in column into two seperate columns?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
The problem is to sort the movies by movie Id and to split the title and year into separate columns in MySQL. Sorting the movies is not an issue but I am trying to separate a column with two sets of data in it. One being the movie title and the other being the year that the movie was released.
Examples being "Toy Story (1998)", "Iron Giant (1999)".
The desired output would be to have a title column and a corresponding year column for these movies.
There are about an upwards of a million columns that I would have to split the movie title and the year for. Is there anyway to this is MySQL?
mysql sql
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
add a comment |
The problem is to sort the movies by movie Id and to split the title and year into separate columns in MySQL. Sorting the movies is not an issue but I am trying to separate a column with two sets of data in it. One being the movie title and the other being the year that the movie was released.
Examples being "Toy Story (1998)", "Iron Giant (1999)".
The desired output would be to have a title column and a corresponding year column for these movies.
There are about an upwards of a million columns that I would have to split the movie title and the year for. Is there anyway to this is MySQL?
mysql sql
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13
add a comment |
The problem is to sort the movies by movie Id and to split the title and year into separate columns in MySQL. Sorting the movies is not an issue but I am trying to separate a column with two sets of data in it. One being the movie title and the other being the year that the movie was released.
Examples being "Toy Story (1998)", "Iron Giant (1999)".
The desired output would be to have a title column and a corresponding year column for these movies.
There are about an upwards of a million columns that I would have to split the movie title and the year for. Is there anyway to this is MySQL?
mysql sql
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
The problem is to sort the movies by movie Id and to split the title and year into separate columns in MySQL. Sorting the movies is not an issue but I am trying to separate a column with two sets of data in it. One being the movie title and the other being the year that the movie was released.
Examples being "Toy Story (1998)", "Iron Giant (1999)".
The desired output would be to have a title column and a corresponding year column for these movies.
There are about an upwards of a million columns that I would have to split the movie title and the year for. Is there anyway to this is MySQL?
mysql sql
mysql sql
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
asked Jan 3 at 5:02
JudgementFlameJudgementFlame
52119
52119
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13
add a comment |
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13
add a comment |
2 Answers
2
active
oldest
votes
We can try using SUBSTRING_INDEX and INSTR here:
SELECT
SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Demo
We isolate the name of the movie by taking a substring up to, but not including, the first space and opening parenthesis. The year is found by taking the final term and removing opening and closing parentheses.
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
add a comment |
I would just use substring_index() for this:
select substring_index(titleyear, ' (', 1) as title,
substring_index(titleyear, ' (', -1) + 0 as year
The second piece uses implicit conversion to store the value as a year.
You can also extract the year as a string using:
substr(titleyear, -5, 4) as year
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
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%2f54016601%2fis-there-a-way-to-seperate-2-values-in-column-into-two-seperate-columns%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
We can try using SUBSTRING_INDEX and INSTR here:
SELECT
SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Demo
We isolate the name of the movie by taking a substring up to, but not including, the first space and opening parenthesis. The year is found by taking the final term and removing opening and closing parentheses.
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
add a comment |
We can try using SUBSTRING_INDEX and INSTR here:
SELECT
SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Demo
We isolate the name of the movie by taking a substring up to, but not including, the first space and opening parenthesis. The year is found by taking the final term and removing opening and closing parentheses.
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
add a comment |
We can try using SUBSTRING_INDEX and INSTR here:
SELECT
SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Demo
We isolate the name of the movie by taking a substring up to, but not including, the first space and opening parenthesis. The year is found by taking the final term and removing opening and closing parentheses.
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
We can try using SUBSTRING_INDEX and INSTR here:
SELECT
SUBSTRING(title, 1, INSTR(title, '(') - 2) AS name,
REPLACE(REPLACE(SUBSTRING_INDEX(title, ' ', -1), '(', ''), ')', '') AS year
FROM yourTable;
Demo
We isolate the name of the movie by taking a substring up to, but not including, the first space and opening parenthesis. The year is found by taking the final term and removing opening and closing parentheses.
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
edited Jan 3 at 5:11
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
answered Jan 3 at 5:06
Tim BiegeleisenTim Biegeleisen
237k13100160
237k13100160
add a comment |
add a comment |
I would just use substring_index() for this:
select substring_index(titleyear, ' (', 1) as title,
substring_index(titleyear, ' (', -1) + 0 as year
The second piece uses implicit conversion to store the value as a year.
You can also extract the year as a string using:
substr(titleyear, -5, 4) as year
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
add a comment |
I would just use substring_index() for this:
select substring_index(titleyear, ' (', 1) as title,
substring_index(titleyear, ' (', -1) + 0 as year
The second piece uses implicit conversion to store the value as a year.
You can also extract the year as a string using:
substr(titleyear, -5, 4) as year
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
add a comment |
I would just use substring_index() for this:
select substring_index(titleyear, ' (', 1) as title,
substring_index(titleyear, ' (', -1) + 0 as year
The second piece uses implicit conversion to store the value as a year.
You can also extract the year as a string using:
substr(titleyear, -5, 4) as year
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
I would just use substring_index() for this:
select substring_index(titleyear, ' (', 1) as title,
substring_index(titleyear, ' (', -1) + 0 as year
The second piece uses implicit conversion to store the value as a year.
You can also extract the year as a string using:
substr(titleyear, -5, 4) as year
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
answered Jan 3 at 12:49
Gordon LinoffGordon Linoff
794k37318421
794k37318421
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%2f54016601%2fis-there-a-way-to-seperate-2-values-in-column-into-two-seperate-columns%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
How clean is the data? For example, how many titles end precisely with seven characters: space, parenthesis, four-digit year, parenthesis?
– rici
Jan 3 at 5:06
There is some variability for instance some title include parentheses such as " 'Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981)" For the most part though, they follow they structure above
– JudgementFlame
Jan 3 at 5:13