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

Jason W · Accepted Answer · 2014-10-23 04:58:13Z

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, 2014 at 4:58

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

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

2 Comments

Dehan Wjiesekara Over a year ago

Great answer.. thumbs up (y)

user2749605 Over a year ago

Hi @Jason W, I need to further extend this function and I expect your help.. please visit the link stackoverflow.com/questions/26522911/…

Collectives™ on Stack Overflow

Replace spaces using function in sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related