Remove anything between first two dashes MySQL2019 Community Moderator ElectionCan I concatenate multiple MySQL rows into one field?How to do this query in mysql?How can I view live MySQL queries?Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?Mysql: Select all data between two datesMySQL - UPDATE query based on SELECT QuerySelect mysql query between date?MySQL: Large VARCHAR vs. TEXT?Delete all Duplicate Rows except for One in MySQL?mysql concat remove trailing dash if it exists
Does splitting a potentially monolithic application into several smaller ones help prevent bugs?
Rejected in 4th interview round citing insufficient years of experience
How could our ancestors have domesticated a solitary predator?
Why is Beresheet doing a only a one-way trip?
Why would a jet engine that runs at temps excess of 2000°C melt when it crashes?
Is there a window switcher for GNOME that shows the actual window?
Is having access to past exams cheating and, if yes, could it be proven just by a good grade?
Grey hair or white hair
Adding an additional "order by" column gives me a much worse plan
Single word request: Harming the benefactor
PTIJ: How can I halachically kill a vampire?
If the Captain's screens are out, does he switch seats with the co-pilot?
"One can do his homework in the library"
Word for a person who has no opinion about whether god exists
How much stiffer are 23c tires over 28c?
Can someone explain what is being said here in color publishing in the American Mathematical Monthly?
Who deserves to be first and second author? PhD student who collected data, research associate who wrote the paper or supervisor?
What to do when during a meeting client people start to fight (even physically) with each others?
Am I not good enough for you?
Is "history" a male-biased word ("his+story")?
Moving plot label
How do I deal with a powergamer in a game full of beginners in a school club?
PTIJ: Why can't I eat anything?
Should I tell my boss the work he did was worthless
Remove anything between first two dashes MySQL
2019 Community Moderator ElectionCan I concatenate multiple MySQL rows into one field?How to do this query in mysql?How can I view live MySQL queries?Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?Mysql: Select all data between two datesMySQL - UPDATE query based on SELECT QuerySelect mysql query between date?MySQL: Large VARCHAR vs. TEXT?Delete all Duplicate Rows except for One in MySQL?mysql concat remove trailing dash if it exists
Good day, I just wanted to ask a help for a select query to select some characters but removing anything between two dashes, for example:
011-12-004 , 011-32-003-B
to
011004 , 011003-B
All I can do is remove from one dash. Please help me.
mysql
asked Mar 7 at 7:44
EllsworthEllsworth
15
add a comment |
Good day, I just wanted to ask a help for a select query to select some characters but removing anything between two dashes, for example:
011-12-004 , 011-32-003-B
to
011004 , 011003-B
All I can do is remove from one dash. Please help me.
mysql
asked Mar 7 at 7:44
EllsworthEllsworth
15
Wait, in your second string there are 3 dashes, why should-003-not be removed? Or the whole-32-003-?
– ALFA
Mar 7 at 7:47
1
Have you tried anything?
– Strawberry
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33
add a comment |
Good day, I just wanted to ask a help for a select query to select some characters but removing anything between two dashes, for example:
011-12-004 , 011-32-003-B
to
011004 , 011003-B
All I can do is remove from one dash. Please help me.
mysql
asked Mar 7 at 7:44
EllsworthEllsworth
15
Good day, I just wanted to ask a help for a select query to select some characters but removing anything between two dashes, for example:
011-12-004 , 011-32-003-B
to
011004 , 011003-B
All I can do is remove from one dash. Please help me.
mysql
mysql
asked Mar 7 at 7:44
EllsworthEllsworth
15
asked Mar 7 at 7:44
EllsworthEllsworth
15
asked Mar 7 at 7:44
EllsworthEllsworth
15
asked Mar 7 at 7:44
EllsworthEllsworth
15
asked Mar 7 at 7:44
EllsworthEllsworth
15
15
Wait, in your second string there are 3 dashes, why should-003-not be removed? Or the whole-32-003-?
– ALFA
Mar 7 at 7:47
1
Have you tried anything?
– Strawberry
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33
add a comment |
Wait, in your second string there are 3 dashes, why should-003-not be removed? Or the whole-32-003-?
– ALFA
Mar 7 at 7:47
1
Have you tried anything?
– Strawberry
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33
Wait, in your second string there are 3 dashes, why should
-003- not be removed? Or the whole -32-003-?– ALFA
Mar 7 at 7:47
Wait, in your second string there are 3 dashes, why should
-003- not be removed? Or the whole -32-003-?– ALFA
Mar 7 at 7:47
1
1
Have you tried anything?
– Strawberry
Mar 7 at 7:48
Have you tried anything?
– Strawberry
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33
add a comment |
2 Answers
2
active
oldest
votes
I'm not quite sure how to differentiate -32- from -003-, I'm guessing you should replace a pattern that matches -(number here)-.
In MySQL 8.0 you can use REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE(text,'-[0-9]+-','',1,0,'c') FROM your_table;
the output will be:
011004
011003-B
answered Mar 7 at 8:16
ALFAALFA
503212
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
add a comment |
This might not be quite right, but close enough...
SET @s = '011-12-004 , 011-32-003-B';
SELECT CONCAT(MID(@s,1,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',1))),MID(@s,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',2))+2,1000))x;
+-----------------------+
| x |
+-----------------------+
| 011004 , 011-32-003-B |
+-----------------------+
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
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%2f55038511%2fremove-anything-between-first-two-dashes-mysql%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'm not quite sure how to differentiate -32- from -003-, I'm guessing you should replace a pattern that matches -(number here)-.
In MySQL 8.0 you can use REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE(text,'-[0-9]+-','',1,0,'c') FROM your_table;
the output will be:
011004
011003-B
answered Mar 7 at 8:16
ALFAALFA
503212
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
add a comment |
I'm not quite sure how to differentiate -32- from -003-, I'm guessing you should replace a pattern that matches -(number here)-.
In MySQL 8.0 you can use REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE(text,'-[0-9]+-','',1,0,'c') FROM your_table;
the output will be:
011004
011003-B
answered Mar 7 at 8:16
ALFAALFA
503212
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
add a comment |
I'm not quite sure how to differentiate -32- from -003-, I'm guessing you should replace a pattern that matches -(number here)-.
In MySQL 8.0 you can use REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE(text,'-[0-9]+-','',1,0,'c') FROM your_table;
the output will be:
011004
011003-B
answered Mar 7 at 8:16
ALFAALFA
503212
I'm not quite sure how to differentiate -32- from -003-, I'm guessing you should replace a pattern that matches -(number here)-.
In MySQL 8.0 you can use REGEXP_REPLACE() function.
SELECT REGEXP_REPLACE(text,'-[0-9]+-','',1,0,'c') FROM your_table;
the output will be:
011004
011003-B
answered Mar 7 at 8:16
ALFAALFA
503212
answered Mar 7 at 8:16
ALFAALFA
503212
answered Mar 7 at 8:16
ALFAALFA
503212
answered Mar 7 at 8:16
ALFAALFA
503212
503212
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
add a comment |
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
too bad the mysql version we used is 5.7 :( but this answer is really appreciated
– Ellsworth
Mar 7 at 8:41
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
Take a look at techras.wordpress.com/2011/06/02/regex-replace-for-mysql, I didn't test it but it could be useful.
– ALFA
Mar 7 at 8:46
add a comment |
This might not be quite right, but close enough...
SET @s = '011-12-004 , 011-32-003-B';
SELECT CONCAT(MID(@s,1,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',1))),MID(@s,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',2))+2,1000))x;
+-----------------------+
| x |
+-----------------------+
| 011004 , 011-32-003-B |
+-----------------------+
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
add a comment |
This might not be quite right, but close enough...
SET @s = '011-12-004 , 011-32-003-B';
SELECT CONCAT(MID(@s,1,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',1))),MID(@s,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',2))+2,1000))x;
+-----------------------+
| x |
+-----------------------+
| 011004 , 011-32-003-B |
+-----------------------+
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
add a comment |
This might not be quite right, but close enough...
SET @s = '011-12-004 , 011-32-003-B';
SELECT CONCAT(MID(@s,1,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',1))),MID(@s,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',2))+2,1000))x;
+-----------------------+
| x |
+-----------------------+
| 011004 , 011-32-003-B |
+-----------------------+
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
This might not be quite right, but close enough...
SET @s = '011-12-004 , 011-32-003-B';
SELECT CONCAT(MID(@s,1,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',1))),MID(@s,CHAR_LENGTH(SUBSTRING_INDEX(@s,'-',2))+2,1000))x;
+-----------------------+
| x |
+-----------------------+
| 011004 , 011-32-003-B |
+-----------------------+
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
answered Mar 7 at 11:27
StrawberryStrawberry
26.6k83150
26.6k83150
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%2f55038511%2fremove-anything-between-first-two-dashes-mysql%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
Wait, in your second string there are 3 dashes, why should
-003-not be removed? Or the whole-32-003-?– ALFA
Mar 7 at 7:47
1
Have you tried anything?
– Strawberry
Mar 7 at 7:48
only -32- which is in the first two dashes should be removed. @ALFA
– Ellsworth
Mar 7 at 7:48
@Strawberry I tried trims, replace and substring_index but I am too far from what I've wanted
– Ellsworth
Mar 7 at 7:49
Substring_index sounds like one way - although for more complex manipulations , you might do better with application code
– Strawberry
Mar 7 at 8:33