一次替换多个值 - 为了将字符串转换为数字。

Question

我试图将一个varchar字段转换为数字，但是这个字段中有一组常见字符需要被删除，以便我成功地将其转换为数值。

该字段的名称是UKSellPrice1。

在将其转换为数字之前，我需要从UKSellPrice1中删除以下字符串：

'.00'
'£'
'n/a'
'$'
'#N/A'

我该如何完成这个任务？

目前我有以下内容：

;WITH R0 AS (

SELECT StyleCode
      ,ColourCode       
      ,UKSellPrice1= CASE WHEN CHARINDEX('.00',UKSellPrice1,1) > 0 
                          THEN REPLACE (UKSellPrice1,'.00','') 
                          ELSE UKSellPrice1 END
      ,UKSellPrice2
 FROM dbo.RangePlan
)
SELECT * 
FROM R0

- Marcello Miorelli

Answer 1

我可以想到两种方法。

第一种方法是使用一堆嵌套的 replace() 语句：

第一种方法是使用一系列嵌套的replace()语句：

select replace(replace(replace(col, '$', ''), '£', ''), 'n/a', '')

等等。

第二个方法是找到第一个数字并从那里开始尝试转换。这需要使用patindex()进行复杂的逻辑处理。以下是一个例子：

select cast(left(substring(col, patindex('%[0-9]%', col), 1000),
                 patindex('%[^0-9]%', substring(col, patindex('%[0-9]%', col), 1000)) - 1
                ) as int)

- Gordon Linoff

Answer 2

您可以通过创建一个函数来去除不需要的字符，实现此操作，代码如下所示：

您可以这样做。创建一个函数以剥离不需要的字符，例如：

CREATE FUNCTION [dbo].[fnRemovePatternFromString](@BUFFER VARCHAR(MAX), @PATTERN VARCHAR(128)) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @POS INT = PATINDEX(@PATTERN, @BUFFER)
    WHILE @POS > 0 BEGIN
        SET @BUFFER = STUFF(@BUFFER, @POS, 1, '')
        SET @POS = PATINDEX(@PATTERN, @BUFFER)
    END
    RETURN @BUFFER
END

然后使用类似于这样的模式在列上调用scalared函数：

;WITH R0 AS (

SELECT StyleCode
      ,ColourCode       
      ,UKSellPrice1= CAST(dbo.fnRemovePatternFromString(UKSellPrice1,'%[£$#N/A.00]%') AS INT)
      ,UKSellPrice2
 FROM dbo.RangePlan
)
SELECT * 
FROM R0

参考资料：

• T-SQL去除所有非字母和非数字字符

- Arion

---

选择 dbo.fnRemovePatternFromString 得到的是 12，而我们期望得到的是 120，因此这个解决方案只适用于单个字符替换。 - Kodak

Answer 3

受@Arion above（谢谢）的启发。 这相当于（n）个REPLACE（REPLACE（REPLACE（）））调用， 因此只是清理代码非常好。 我将STUFF（）替换为REPLACE（），因此它比您上面的示例更通用。经过测试，能够正常工作（对于我的数据集），但我不是SQL大师，所以可能会有差异。此外，由于它只是在底层使用REPLACE（），因此可以处理任何大小的字符串替换，而不仅仅是一个字符（如@Arion的示例）。

/*
.SYNOPSIS
    FIND/REPLACE MULTIPLE VALUES FROM A STRING

.DESCRIPTION
    Walk through the @FINDChars pattern, one character at a time.
    Do a find + REPLACE() on each of those characters in the string.

.EXAMPLE
    [dbo].[fnFindReplaceAnyMatch](MyColumnName, '(''")', '')
*/
CREATE FUNCTION [dbo].[fnFindReplaceAnyMatch](
    @BUFFER         VARCHAR(MAX), 
    @FINDChars      VARCHAR(128),
    @REPLACEChar    VARCHAR(128)
) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @SingleCharPattern VARCHAR(1);
    DECLARE @PatternIndex INT = 0;
    WHILE @PatternIndex <= LEN(@FINDChars) BEGIN
        SET @SingleCharPattern = SUBSTRING(@FINDChars, @PatternIndex, 1);
        SET @BUFFER = REPLACE(@BUFFER, @SingleCharPattern, @REPLACEChar);
        SET @PatternIndex = @PatternIndex + 1;
    END
    RETURN @BUFFER
END
GO

- m1m1k

---

函数的文档风格是否被某些文档生成器所支持？如果是，那么这个文档生成器的名称是什么？ - Be Kind To New Users