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: 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

Gordon Linoff · Accepted Answer · 2014-04-22 11:53:03Z

72

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)

answered Apr 22, 2014 at 11:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:15:38Z

7

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

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Apr 22, 2014 at 11:47

Arion

31.3k10 gold badges75 silver badges89 bronze badges

1 Comment

Kodak Over a year ago

select dbo.[fnRemovePatternFromString]('120', '%[£$#N/A.00]%') gives 12 while one expects 120 so this solution should actually be used for single character replacements only

Marcello Miorelli · Accepted Answer · 2021-11-12 16:51:30Z

1

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

edited Nov 12, 2021 at 16:51

Marcello Miorelli

3,7906 gold badges53 silver badges79 bronze badges

answered Nov 11, 2021 at 19:28

m1m1k

1,45514 silver badges15 bronze badges

1 Comment

Be Kind To New Users Over a year ago

Does the documentation style of the function supported by some documentation generator and if so, what is that documentation generator's name?

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related