once a friend of mine asking how to replace space character between words using query..i say it's easy just use RTRIM and LTRIM function but again he said what about replace the character after that?
RTRIM and LTRIM only remove leading and trailing spaces, let say u have this word ' a b c d ', using RTRIM and LTRIM only make it something like this 'a b c d'. what about spaces after the word 'a', 'b', so on?? i thought it was easy, using REPLACE then but that's not what he want because REPLACE only make the word is non-readable so i'm thinking using something like this
PS: play with @Initial, @CharToFind and @ReplaceChar
until then
RTRIM and LTRIM only remove leading and trailing spaces, let say u have this word ' a b c d ', using RTRIM and LTRIM only make it something like this 'a b c d'. what about spaces after the word 'a', 'b', so on?? i thought it was easy, using REPLACE then but that's not what he want because REPLACE only make the word is non-readable so i'm thinking using something like this
DECLARE @INITIAL VARCHAR(100)
DECLARE @Rest VARCHAR(100)
DECLARE @Final VARCHAR(100)
DECLARE @CharToFind VARCHAR(5)
DECLARE @ReplaceChar VARCHAR(5)
DECLARE @found INT
SET @Initial = ' a,,b,,,c,,,,,,,,,,d,,,,,,,, '
SET @Final = ''
SET @CharToFind =','
SET @ReplaceChar ='.'
SELECT @Rest = REPLACE(RTRIM(LTRIM(@Initial)), @CharToFind, @ReplaceChar)
SELECT @found = CHARINDEX(REPLICATE(@ReplaceChar, 2), @Rest)
WHILE @found > 0
BEGIN
SELECT @Final = @Final + SUBSTRING(@Rest, 1, @found - 1)
SELECT @Rest = SUBSTRING(@Rest, @found + 1, Len(@Rest))
SELECT @found = CHARINDEX(REPLICATE(@ReplaceChar, 2), @Rest)
END
SELECT @Final = @Final + @Rest
SELECT RTRIM(LTRIM(REPLACE(@Final, @ReplaceChar, ' ')))
PS: play with @Initial, @CharToFind and @ReplaceChar
until then
Comments