SQL - Replace non numeric characters in string

Question

I'm writing a function that takes a string and has to replace any non-numeric character with two numeric characters taken from a table. This is the code I use to explore the string and find the non-numeric characters:

SET @string = '1a2b3c4d'
SET @wcount= 0
SET @index = 1
SET @len= LEN(@string)

WHILE @index<= @len
 BEGIN
    SET @char = SUBSTRING(@string, @index, 1)
    IF @char LIKE '%[a-z]%'
      PRINT 'char ' + CONVERT(varchar(10), @index)
    ELSE
      PRINT @char
    SET @index= @index+ 1
 END

The output is the following

1
char 2
2
char 4
3
char 6
4
char 8

Now, when I find a non-numeric character I have to replace it with two numeric chars taken from a table by a select. E.g.

SELECT @temp = REPLACEMENT FROM Conversion_Tab WHERE EXPR = @char

In conclusion, if I have the following string '1a2a3a4a' and the replacement for 'a' is '88' the resulting string should be '188288388488'

Thanks in advance for any help.

Bye!

bvr · Accepted Answer · 2013-07-19 08:43:42Z

2

Try this

DECLARE @string VARCHAR(100)
DECLARE @outstring VARCHAR(100)
DECLARE @wcount INT
DECLARE @temp INT
DECLARE @index INT
DECLARE @len INT
DECLARE @char CHAR

SET @string = '1a2a3a4a'
SET @wcount= 0
SET @index = 1
SET @len= LEN(@string)

SET @outstring = ''

WHILE @index<= @len
 BEGIN
    SET @char = SUBSTRING(@string, @index, 1)
    IF @char LIKE '%[a-z]%'
    BEGIN
      SELECT @temp = REPLACEMENT FROM #Conversion_Tab WHERE EXPR = @char
      SET @outstring = @outstring + CONVERT(VARCHAR(10),@temp)
     END
    ELSE
    BEGIN
      SET @outstring = @outstring + @char 
    END
    SET @index= @index+ 1

END

SELECT @outstring

SQL FIDDLE DEMO

answered Jul 19, 2013 at 8:43

bvr

4,8361 gold badge23 silver badges24 bronze badges

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

Comments

SteveB · Accepted Answer · 2013-07-19 08:44:40Z

looks like you need isnumeric(). so if not isnumeric(char) replace it with your lookup value.

taken from the other answer but produces the same result

DECLARE @string VARCHAR(100)
DECLARE @outstring VARCHAR(100)
DECLARE @wcount INT
DECLARE @temp INT
DECLARE @index INT
DECLARE @len INT
DECLARE @char CHAR

SET @string = '1a2a3a4a'
SET @wcount= 0
SET @index = 1
SET @len= LEN(@string)

SET @outstring = ''

WHILE @index<= @len
 BEGIN
    SET @char = SUBSTRING(@string, @index, 1)
    IF ISNUMERIC(@char) = 0
    BEGIN
      SELECT @temp = REPLACEMENT FROM #Conversion_Tab WHERE EXPR = @char
      SET @outstring = @outstring + CONVERT(VARCHAR(10),@temp)
     END
    ELSE
    BEGIN
      SET @outstring = @outstring + @char 
    END
    SET @index= @index+ 1

END

Collectives™ on Stack Overflow

SQL - Replace non numeric characters in string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related