Search code examples
mysqlsqltrimcut

Delete part of a string in SQL

I have a table with a field with this kind of entries:

"text text text"
"text text - Cap. 125"
"text - Cap. 1"

I need to cut " - Cap. (Number)".

As you can see in some cases there's no "Cap." and in other cases the number has more than 1 digit.

I tried to use REPLACE() and TRIM(), but REPLACE() only works for " - cap." but not for the numbers; and TRIM() cut a specific number of characters.

Also, I think I need something like WHERE field LIKE '% - CAP.%' to verify that I'm changing the right field, because not all the fields need to be changed.

Any suggestions?

Solution 
  • SELECT CASE
         WHEN myField RLIKE ' - Cap\\. [[:digit:]]{1,3}$' THEN
           LEFT(myField, 1
             + CHAR_LENGTH(myField)
             - CHAR_LENGTH(' - Cap. ')
             - LOCATE(REVERSE(' - Cap. '), REVERSE(myField))
           )
         ELSE myField
       END
FROM   myTable

    See it on sqlfiddle.