Replace spaces using function in sql

Question

I'am currently developing a program and i want to write a function which is accept a value in following format

"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH"

I want to replace the spaces in above with "_" and need a output as showed following format (please note that this should happen only for string which is inside double quotes)

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF_GGGG_HHHH"

can anybody help me with this

score 3 · Accepted Answer · answered Oct 23 '14 at 04:58

3

Here's the logic for it you could add to a function as you wanted.

DECLARE @In VARCHAR(50) = '"AAAA BBBB"  CCCC DDDD EEEE "FFFF GGGG HHHH"'

DECLARE @Quote SMALLINT = -1, @Index INT = 1, @Char CHAR(1)
WHILE @Index <= LEN(@In) BEGIN
    SET @Char = SUBSTRING(@In, @Index, 1)
    IF @Char = '"'
        SET @Quote = @Quote * -1
    IF @Char = ' ' AND @Quote > 0
        SET @In = STUFF(@In, @Index, 1, '_')
    SET @Index = @Index + 1
END

PRINT @In

Output

"AAAA_BBBB"  CCCC DDDD EEEE "FFFF_GGGG_HHHH"

answered Oct 23 '14 at 04:58

Jason W

13,026
3
31
62

Great answer.. thumbs up (y) – Dehan Wjiesekara Oct 23 '14 at 06:03
Hi @Jason W, I need to further extend this function and I expect your help.. please visit the link https://stackoverflow.com/questions/26522911/sql-character-replace-function-extend – user2749605 Oct 23 '14 at 07:09

Replace spaces using function in sql

1 Answers1

Linked