I am sitting on a database where one column contains two data types ;)
"1";"When the Wind Blows 0275";"James Patterson " "4";"The Reluctant Fundamentalist 1026";"Mohsin Hamid";
The user thought it would be easily searchable this way...
Now I am trying to fix this, taking the ID out of the title field and putting it into it's own column.
How do I select the title with a regexp so that I only get the four digit number at the end of the title? That number can start with a zero and the title could contain more digits than just those 4.
There is also no guarantee that the digits are the last four characters in the title, sometimes there are some spaces following.
SUBSTRING will not work in this case
thanks for help!