SQL 同时替换多个值 - 为了将字符串转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23218337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
replace multiple values at the same time - in order to convert a string to a number
提问by Marcello Miorelli
I am trying to convert a varchar field to a number, however, there is a set of common characters inside that field that need to be removed in order for me to successfully convert it to numeric.
我正在尝试将 varchar 字段转换为数字，但是，该字段中有一组常用字符需要删除，以便我成功将其转换为数字。
the name of the field is UKSellPrice1
该字段的名称是 UKSellPrice1
I need to remove the following strings from UKSellPrice1 BEFORE converting it to numeric:
在将其转换为数字之前，我需要从 UKSellPrice1 中删除以下字符串：
'.00'
'￡'
'n/a'
'$'
'#N/A'
How can I get this done?
我怎样才能做到这一点？
at the moment I have the following:
目前我有以下几点：
;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
回答by Gordon Linoff
I can think of two approaches.
我可以想到两种方法。
The first is to use a bunch of nested replace()statements:
第一种是使用一堆嵌套replace()语句：
select replace(replace(replace(col, '$', ''), '￡', ''), 'n/a', '')
and so on.
等等。
The second is to find the first digit and try converting from there. This requires complicated logic with patindex(). Here is an example:
第二个是找到第一个数字并尝试从那里转换。这需要复杂的逻辑patindex()。下面是一个例子：
select cast(left(substring(col, patindex('%[0-9]%', col), 1000),
patindex('%[^0-9]%', substring(col, patindex('%[0-9]%', col), 1000)) - 1
) as int)
回答by Arion
You could do this. Create a function to strip a way the unwanted chars like this:
你可以这样做。创建一个函数来去除不需要的字符，如下所示：
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
Then call the scalared function on the column with a pattern like this:
然后使用如下模式调用列上的标量函数：
;WITH R0 AS (
SELECT StyleCode
,ColourCode
,UKSellPrice1= CAST(dbo.fnRemovePatternFromString(UKSellPrice1,'%[￡$#N/A.00]%') AS INT)
,UKSellPrice2
FROM dbo.RangePlan
)
SELECT *
FROM R0
Reference:
参考：
