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.
A:
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:
Find the position of the first letter from the end (e.g. the minimum of the 25 LOCATEs on the string's reverse)
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
glebm
2010-04-02 18:57:57
I suppposed so for the first time, but later I began to think may be there are some standard functions that can help or use regular expressions. It looks like regulare expressions won't help here because in MYSQL this functions return o or 1.
2010-04-02 19:02:49
Yeah, Regexp replace isn't a part of MySQL, unfortunately.However, things like this are usually better to be done on the db client (i.e. your application).
glebm
2010-04-02 19:09:04
It seems to me sometimes it can be useful of the server. For example, you need to make aforementioned task for every row of the table.
2010-04-02 19:16:02
Well, if you don't have access to UDF, I suppose you could just do trim based on results of `LOCATE("0", REVERSE(@string)), LOCATE("1", REVERSE(@string)),... LOCATE("9", REVERSE(@string))`
glebm
2010-04-02 19:24:08
It looks like it can't be done without cycles. We can have mulitple digits: 2gala9999882458.
2010-04-02 19:32:20
Then you could `LOCATE("a", @string_reverse)...LOCATE("z", @string_reverse)`, as long as you only have small latin letters. Are there any reasons not to use UDF?
glebm
2010-04-02 19:35:34
Frankly speaking, the only reason is that I have not used them before.I'll check them out.
2010-04-02 19:57:28
+1
A:
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. :-)
Ron Savage
2010-04-02 20:01:53