0

I am sitting on a database where one column contains two data types ;)

Example

"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!

2

If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function:

SELECT
  RIGHT(RTRIM(YourColumnName), 4) AS SomeCode
FROM
  YourTableName
WHERE
  ...
;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.