replace multiple values at the same time - in order to convert a string to a number

Question

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.

the name of the field is UKSellPrice1

I need to remove the following strings from UKSellPrice1 BEFORE converting it to numeric:

'.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 

Answer 1

I can think of two approaches.

The first is to use a bunch of nested replace() statements:

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:

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

Answer 2

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:

• T-SQL strip all non-alpha and non-numeric characters