0

I have the following column in mysql table:

NoRegProp
-------------
NULL
62
135/عرسال
31المنية
التبانة  1132

which is not organized at all. I want to separated the numeric values from the string values in 2 different columns as follow:

numberReg               placeReg
NULL                    NULL
62                      NULL
135                     عرسال
31                      المنية
1132                    التبانة

Please any help?

CorpPlus
  • 7
  • 3
  • If *you* can't identify and describe a pattern to automatically split by, what makes you think we can? We know much less about your data than we do. – jpmc26 Dec 22 '16 at 09:05
  • i used this query to extract the numeric values, but i don't know how to extract the string values: `code`UPDATE CARMDI SET `numberReg` = CONVERT( `NoRegProp` , SIGNED INTEGER );`code` – CorpPlus Dec 22 '16 at 09:13
  • you did it for integer and now for string: http://stackoverflow.com/questions/11431831/remove-all-numeric-characters-from-column-mysql – Riad Dec 22 '16 at 09:19

1 Answers1

0
select 
   cast(NoRegProp as signed) as numberReg, 
   substring(NoRegProp, CHAR_LENGTH(cast(NoRegProp as signed)) + 1,  CHAR_LENGTH(NoRegProp)) as placeReg 
from yourtablename

For String sanitizing(removing / etc) you can further use REPLACE

Alexey
  • 2,388
  • 1
  • 16
  • 32
  • this query is good but it won't give a good result if i have this format : 65/75حالات , it returns numberReg = 65 ; placeReg = /75حالات , which i need only the placeReg = حالات – CorpPlus Dec 22 '16 at 09:53
  • consider doing replace before cast - replace(NoRegProp, '/', '') – Alexey Dec 22 '16 at 09:55