Need to Separate the String with a separator of - in MYSQL

Question

I have this string 1111-2222-3-4-55-12345678901234567 1 in MYSQL Table Field.

What I need to do is to separate first 5 parameters separated with a -. Like I need to Separate:

score 4 · Answer 1 · answered Oct 13 '15 at 13:35

4

$stringElements = explode('-', $string);
echo $stringElements[0];// 1111
echo $stringElements[1];// 2222
echo $stringElements[2];// 3
echo $stringElements[3];// 4
echo $stringElements[4];// 55
$stringElements[5];// 12345678901234567 1

answered Oct 13 '15 at 13:35

Kanti

627
4
18

I need to do it in SELECT STATEMENT in MY SQL :-( it works perfectly in PHP but if you can hep in QUERY of MySQL please – Raheel Khan Lodhi Oct 13 '15 at 13:45
2

Then why did you include php in the tag list? – Shadow Oct 13 '15 at 13:47

Shadow · Answer 2 · 2015-10-13T13:47:01.923

0

If the length of each section within the string is constant, then

select replace(left(fieldname,16),'-','') as alias from table

will do the trick.

UPDATE:

If you need to have each individual value in a separate field, then it's a slightly more complicated:

SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(fieldname, '-', 1), LENGTH(SUBSTRING_INDEX(fieldname,'-', 0)) + 1) as first,
REPLACE(SUBSTRING(SUBSTRING_INDEX(fieldname, '-', 2), LENGTH(SUBSTRING_INDEX(fieldname,'-', 1)) + 1) as second,
...
FROM table

edited Oct 13 '15 at 13:47

answered Oct 13 '15 at 13:36

Shadow

33,525
10
51
64

If the length is variable then how I will handle this ?> – Raheel Khan Lodhi Oct 13 '15 at 13:44
I just tried to take first from the string but it has so many errors in query. – Raheel Khan Lodhi Oct 13 '15 at 13:55
if to be done multiple times better create function for it eg SPLIT_STR as given in this http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two and http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ – narendra Oct 13 '15 at 14:05
@RaheelKhanLodhi if you do not share the actual sql statement you tried and provide the received error message, then we cannot really help you. – Shadow Oct 13 '15 at 14:12

score 0 · Accepted Answer · answered Oct 13 '15 at 14:03

Even though I did not test it, this should solve your problem.

SELECT `mystring`, 
    SUBSTRING_INDEX(mystring,'-',1) AS part1, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(mystring,'-',2),'-',-1) AS part2, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(mystring,'-',3),'-',-1) AS part3, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(mystring,'-',4),'-',-1) AS part4, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(mystring,'-',5),'-',-1) AS part5, 
FROM my_table;

------------------------------------------------------------------------------
| mystring                           | part1 | part2 | part3 | part4 | part5 |
------------------------------------------------------------------------------
| 1111-2222-3-4-55-12345678901234567 | 1111  | 2222  | 3     | 4     | 55    |

This is splitting the text with an increased index count and re-splitting it from the last index for each part through part2 to part5

score 0 · Answer 4 · answered Oct 13 '15 at 14:05

You can create function:

CREATE FUNCTION SPLIT_STR(
   x VARCHAR(255),
   delim VARCHAR(12),
   pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
   LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
   delim, '');

and then use it like

SELECT SPLIT_STR('1-2-3', '-', 1);

it will return 1. With all this examples you can easily build your own query

Source: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

score 0 · Answer 5 · answered Oct 13 '15 at 14:08

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX('1111-2222-3-4-55-12345678901234567','-',i+1),'-',-1) x FROM ints;
+-------------------+
| x                 |
+-------------------+
| 1111              |
| 2222              |
| 3                 |
| 4                 |
| 55                |
| 12345678901234567 |
+-------------------+

Need to Separate the String with a separator of - in MYSQL

5 Answers5