February 10, 2004 at 7:57 am
I had a character string with imbedded blanks. I want to change the embedded blanks to a single comma. I can use the Replace function to replace each of the blanks with a comma, but then I get one comma for each blank. I want only one comma no matter how many blanks there are is a row. Can someone help me? Thanks.
February 10, 2004 at 9:18 am
There are at least two ways to do this. Easy but may not too effiecient. Just call replace and replace 16 blanks with one blank. Then call it again on the result and replace 4 blanks with 1 blank. Call it twice more replac ing 2 blanks with one blank. Then replace the blank with a comma. This will work for string of blanks up to 256. The other way is to write a user defined function that simply detects consecutive blanks and builds a result string that excludes them. Just loop throught the string and if the previous char was a blank don't include the char in the result. If the char is the first blank the result is a comma.
February 10, 2004 at 10:36 am
CREATE FUNCTION dbo.f_RepRuns(@InStr varchar(8000),@Remove varchar,@Delimiter char)
RETURNS varchar(8000) BEGIN
DECLARE @OutStr varchar(8000)
IF @Remove = '' SET @Remove = ' '
SET @OutStr = @InStr
WHILE @OutStr LIKE '%' + @Remove + @Remove + '%'
SET @OutStr = REPLACE(@OutStr,@Remove + @Remove,@Remove)
SET @OutStr = REPLACE(@OutStr,@Remove,@Delimiter)
RETURN @OutStr END
--Jonathan
February 10, 2004 at 11:02 am
Another way.... (un-comment print statements to watch it work)
CREATE PROCEDURE spStripSpacesToComma
@sVariable varchar(50)
,@sReturn varchar(50) OUTPUT
as
declare @iPtr integer
set @iPtr = 0
select @iPtr = charindex(' ', @sVariable, @iPtr)
while @iPtr > 0
begin
-- print 'Before: Space at: ' + ltrim(str(@iPtr)) + ' String-->' + @sVariable + '<--'
if substring(@sVariable, @iPtr-1, 1) = ','
set @sVariable = substring(@sVariable, 1, @iPtr-1) + substring(@sVariable, @iPtr+1, len(@sVariable))
else
set @sVariable = substring(@sVariable, 1, @iPtr-1) + ',' + substring(@sVariable, @iPtr+1, len(@sVariable))
while substring(@sVariable, @iPtr+1, 1) = ' '
begin
set @sVariable = substring(@sVariable, 1, @iPtr-1) + substring(@sVariable, @iPtr+1, len(@sVariable))
-- print '-->' + @sVariable + '<--'
end
-- print 'After: Space at: ' + ltrim(str(@iPtr)) + ' String-->' + @sVariable + '<--'
select @iPtr = charindex(' ', @sVariable, @iPtr)
end
set @sReturn = @sVariable
September 9, 2004 at 6:14 pm
Taking mwelcome's suggestion to a full 8000 varchar field containing only the first and last characters as non-blanks, you can:
As in (to prove this out):
DECLARE @v varchar(8000), @vr-2 varchar(8000)
SELECT @v = 'x' + REPLICATE(' ',7998) + 'y'
SELECT @vr-2 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@v,' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' ')
SELECT @v
SELECT @vr-2
I ran this on four fields of a 29K row sample table and it added one second to the query elapsed time, so it's highly efficient.
For the sake of "complete" testing, the following iterates through strings of 1 to 7998 blanks bound by 'x' and 'y' and publishes only those results where a pair of blanks exists in the result field (@vr):
DECLARE @v varchar(8000), @vr-2 varchar(8000)
DECLARE @i int
SELECT @i = 1
WHILE @i <= 7998
BEGIN
SELECT @v = 'x' + REPLICATE(' ',@i) + 'y'
SELECT @vr-2 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@v,' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' ')
IF CHARINDEX(' ',@vr) > 0
BEGIN
SELECT 'For ' + CAST(@i AS varchar) + ', found double-blank:'
SELECT @vr-2
END
SELECT @i = @i + 1
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply