从字符串中去除非数字字符 [英] Strip non-numeric characters from a string

问题描述

我目前正在做一个数据转换项目，需要从字符串中去除所有字母字符.不幸的是，我无法创建或使用函数，因为我们不拥有源计算机，因此我从搜索以前的帖子中找到的方法无法使用.

I'm currently doing a data conversion project and need to strip all alphabetical characters from a string. Unfortunately I can't create or use a function as we don't own the source machine making the methods I've found from searching for previous posts unusable.

在 select 语句中执行此操作的最佳方法是什么?速度不是太大问题，因为这只会运行超过 30,000 条记录，并且是一次性声明.

What would be the best way to do this in a select statement? Speed isn't too much of an issue as this will only be running over 30,000 records or so and is a once off statement.

推荐答案

RichardTheKiwi's script in a function for use in selects without cross apply,还添加了点，因为在我的情况下，我将它用于 varchar 字段中的 double 和 money 值

RichardTheKiwi's script in a function for use in selects without cross apply, also added dot because in my case I use it for double and money values within a varchar field

CREATE FUNCTION dbo.ReplaceNonNumericChars (@string VARCHAR(5000))
RETURNS VARCHAR(1000)
AS 
    BEGIN
        SET @string = REPLACE(@string, ',', '.')
        SET @string = (SELECT   SUBSTRING(@string, v.number, 1)
                       FROM     master..spt_values v
                       WHERE    v.type = 'P'
                                AND v.number BETWEEN 1 AND LEN(@string)
                                AND (SUBSTRING(@string, v.number, 1) LIKE '[0-9]'
                                     OR SUBSTRING(@string, v.number, 1) LIKE '[.]')
                       ORDER BY v.number
                      FOR
                       XML PATH('')
                      )
        RETURN @string
    END
GO

感谢 RichardTheKiwi +1

Thanks RichardTheKiwi +1

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