如何从字符串中删除任何尾随数字? [英] How to remove any trailing numbers from a string?

问题描述

样本输入:

你好吗"

纽约排名第一的披萨店是什么?"

"What is the #1 pizza place in NYC?"

多米诺骨牌是第一"

废话 123123"

更多废话 12321 123123 123132"

"More blah 12321 123123 123132"

预期输出:

你好吗"

纽约排名第一的披萨店是什么?"

"What is the #1 pizza place in NYC?"

多米诺骨牌就是数字"

废话"

更多废话"

我认为这是一个两步过程:

I'm thinking it's a 2 step process:

1. 将整个字符串拆分为字符，每个字符一行(包括空格)，以反向顺序
2. 循环，如果是空格或数字，则跳过，否则添加到另一个数组的开始.

我应该得到想要的结果.

And i should end up with the desired result.

我可以想到一些快速而肮脏的方法，但这需要执行得相当好，因为它是一个在繁忙表上运行的触发器，所以我想我会把它扔给 T-SQL 专家.

I can think of a few quick and dirty ways, but this needs to perform fairly well, as it's a trigger that runs on a busy table, so thought i'd throw it out to the T-SQL pros.

有什么建议吗?

推荐答案

这个解决方案应该更高效一点，因为它首先检查字符串是否包含数字，然后检查字符串是否以数字结尾.

This solution should be a bit more efficient because it first checks to see if the string contains a number, then it checks to see if the string ends in a number.

 CREATE FUNCTION dbo.trim_ending_numbers(@columnvalue AS VARCHAR(100)) RETURNS VARCHAR(100)
    BEGIN
    --This will make the query more efficient by first checking to see if it contains any numbers at all
    IF @columnvalue NOT LIKE '%[0-9]%'
        RETURN @columnvalue

    DECLARE @counter INT
    SET @counter = LEN(@columnvalue)

    IF ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 0
        RETURN @columnvalue 

    WHILE ISNUMERIC(SUBSTRING(@columnvalue,@counter,1)) = 1 OR SUBSTRING(@columnvalue,@counter,1) = ' '
    BEGIN
        SET @counter = @counter -1
        IF @counter < 0
            BREAK
    END
    SET @columnvalue = SUBSTRING(@columnvalue,0,@counter+1)

    RETURN @columnvalue
    END

如果你跑

SELECT dbo.trim_ending_numbers('More blah 12321 123123 123132')

它会回来

'More blah'

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