CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Marcello Miorelli
Marcello Miorelli

Reputation: 3678

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

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: enter image description here

;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

Upvotes: 42

Views: 211855

Answers (3)

m1m1k
m1m1k

Reputation: 1435

Inspired by @Arion above (thanks). This is the equivalent of (n) number of REPLACE(REPLACE(REPLACE())) calls, so just cleans up the code really nicely. I've swapped out STUFF() for REPLACE() so it's slightly more general use than your example above. Tested working (for my data sets), but I'm not a SQL guru, so YMMV. Also, since it just uses REPLACE() under the hood this handles Replacement with any size string, not just 1 char (as with @Arion's example).

/*
.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

Upvotes: 1

Arion
Arion

Reputation: 31239

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:

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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)

Upvotes: 71

Related Questions