如何使用SQL从字符串的末尾删除数字 [英] How to remove digits from the end of the string using SQL

问题描述

请，你能回答我的问题吗? 如何使用SQL从字符串的末尾删除数字? 例如，字符串"2Ga4la2009"必须转换为2Ga4la.问题在于我们无法修剪它们，因为我们不知道字符串末尾有多少个数字. 最好的问候，Galina.

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.

推荐答案

这似乎可行:

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

concat('myvalue'，'1')用于防止以0结尾的数字.

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.

现在，您知道数字部分的长度，并且可以使用"left()"函数确定要截取的原始值的字符数.

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. :-)

这篇关于如何使用SQL从字符串的末尾删除数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！