How to remove digits from the end of the string us

Question

Please, could you answer my question. How to remove digits from the end of the string using SQL? For example, the string '2Ga4la2009' must be converted to 2Ga4la. The problem is that we can't trim them because we don't know how many digits are in the end of the string. Best regards, Galina.

Answer 1

This seems to work:

select left( concat('2Ga4la2009','1'), length(concat('2Ga4la2009','1')) - length(convert(convert(reverse(concat('2Ga4la2009','1')),unsigned),char)))

The concat('myvalue', '1') is to protect against numbers that end in 0s.

The reverse flips it around so the number is at the front.

The inner convert changes the reversed string to a number, dropping the trailing chars.

The outer convert turns the numeric part back to characters, so you can get the length.

Now you know the length of the numeric portion, and you can determine the number of characters of the original value to chop off with the "left()" function.

Ugly, but it works. :-)

Answer 2

Take a look at this: http://www.mysqludf.org/lib_mysqludf_preg/

And if you for some reason can't use UDF, and don't want to do it on the db client side, you can always do the following:

1. Find the position of the first letter from the end (e.g. the minimum of the 25 LOCATEs on the string's reverse)

2. Do LEFT(@string, @string_length - @result_of_step_1)

You don't have to do any special handling in case there aren't any digits at the end of the string because in this case LOCATE returns 0.

Cheers