CODEWITHSUNDEEP
sqlstringreplacecharacter
lugeno
lugeno

Reputation: 1065

SQL - Replace non numeric characters in string

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!

Upvotes: 0

Views: 1950

Answers (2)

SteveB
SteveB

Reputation: 1514

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

Upvotes: 0

bvr
bvr

Reputation: 4826

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

Upvotes: 2

Related Questions