-1

I have an column name phone1 which contain phone#

0345123456,032145678
0345221123,032443332
0347886543,038875532
0345776767

I have to seperate Phone# after "," in another column name phone 2 for example 

0345123456 in  phone1 
032145678  in  phone2
Muhammad Saad
  • 21
  • 4
  • 2
    Do not store values as CSV. Learn about normalization – Jens Sep 05 '18 at 06:13
  • 4
    Possible duplicate of [Split value from one field to two](https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two) – Jens Sep 05 '18 at 06:14

3 Answers3

0

from sample data it seems to me you need below

   select
    SUBSTRING_INDEX(phone#, ",", -1) as phone1,        
    SUBSTRING_INDEX(phone#, ",", 1) as phone2 from table1
    where phone# like '%,%'
    union all
    select phone# as phone1,  '' as phone2  fraon table1  
     where phone# not like '%,%'
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
-1

using substring_index you can splix strings based on delimiter

substring_index(`#phone`,',',1) as phone1,substring_index(`#phone`,',',-1) as phone2

this is aplicable only if u know how much to be delimited

p.ganesh
  • 120
  • 4
-1

Try this:

  SELECT concat(Substring(phone, 1, Locate(',', phone) - 1),' in phone1') AS 
            phone1, 
           concat(Substring(phone, Locate(',', phone) + 1),' in phone2')   AS phone2
    FROM   tablename where phone not like '%,%'
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • what is the result of your query if there is no `,` in the value? like the last line of op's sample data? – Jens Sep 05 '18 at 06:33
  • 1
    yes, i need to filtered it, thank u. But one question to u - do you think giving downvote is more suitable than commenting about someone's mistake – Fahmi Sep 05 '18 at 06:35