August 20, 2003 at 11:24 am
I would like to find spaces and remove them from a last name field in my DB.
Ex. St James is entered - I would like to change the string to Stjames.
I'm not exactly sure how to do this?
Would I use the substring function?
August 20, 2003 at 12:28 pm
Here's a simple one. It will only remove one space, but it will work for mulptiple spaces if you run it multiple times.
declare @strName varchar(100)
select @strName = 'St James'
select left(@strName,(charindex(' ',@strName)-1))+right(@strName,(len(@strName)-charindex(' ',@strName)))
August 20, 2003 at 12:32 pm
Works like a charm, thanks!
August 20, 2003 at 12:34 pm
Here is a method to remove large amounts of White space in a with a single commands that contains multiple REPLACE clauses:
http://www.geocities.com/sqlserverexamples/#string2
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 20, 2003 at 12:50 pm
quote:
Works like a charm, thanks!
Thanks! This is my first time making suggestions on this site. I always come here for questions instead 🙂 Here's a loop to remove multiple spaces...
-------------------------------------------
declare @strName varchar(100)
select @strName = ' S t J a m e s '
while charindex(' ',@strName) > 0
begin
select @strName = ltrim(@strName)
select @strName = rtrim(@strName)
select @strName = left(@strName,(charindex(' ',@strName)-1))+right(@strName,(len(@strName)-charindex(' ',@strName)))
end
select @strName
-------------------------------------------
Edited by - fubak on 08/20/2003 1:13:54 PM
August 20, 2003 at 1:54 pm
Could you use SET @stName = REPLACE( @stName, ' ', '' ) ?
Guarddata
August 20, 2003 at 2:54 pm
Greg Larsen and guarddata, your TSQL suggestions work great as well. Thanks a bunch to all.
August 20, 2003 at 3:03 pm
quote:
Could you use SET @stName = REPLACE( @stName, ' ', '' ) ?Guarddata
Darn you and your common sense! 🙂
August 20, 2003 at 3:33 pm
I made something similar and built it into a function
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'udf_RemoveMultChars')
DROP FUNCTION udf_RemoveMultChars
GO
CREATE FUNCTION udf_RemoveMultChars
(@Pattern varchar(10), -- This is the pattern to search on. i.e. ' '
@StringWithMultChars varchar(255), -- This is the string to have multiples removed
@RemoveAll bit) -- Set to 1 if you want all instances of the pattern removed
RETURNS varchar(255)
AS
BEGIN
declare @ReplacementString varchar(10),
@SearchPattern varchar(20)
If @RemoveAll = 0
begin
-- remove all instances of the pattern but one.
set @ReplacementString = @Pattern
set @SearchPattern = @Pattern+@Pattern
end
else
begin
-- To remove all instances of a pattern set @ReplacmentPattern = ''
set @ReplacementString = ''
set @SearchPattern = @Pattern
end
while CHARINDEX(@SearchPattern, @StringWithMultChars) <> 0
begin
set @StringWithMultChars = replace(@StringWithMultChars, @SearchPattern, @ReplacementString)
end
RETURN @StringWithMultChars
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.udf_RemoveMultChars
(' ', 'asdf asdf asdf asdf', 1)
GO
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 21, 2003 at 2:52 pm
That's to complicated. This works beautifuly.
declare @String varchar(255)
set @String = 'Have a nice day'
While 1 = 1
begin
set @String = replace(@String, ' ', '')
IF charindex(' ', @String) = 0 Break
end
select @String
Signature is NULL
August 21, 2003 at 3:08 pm
Question. If you are replacing all the spaces with no space with the command "replace(@String, ' ', '')", then why do you need the while loop, with the break logic? Isn't this code even simpler?
declare @String varchar(255)
set @String = 'Have a nice day'
set @String = replace(@String, ' ', '')
select @string
This code even works if there is more than a single space between each word.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 21, 2003 at 3:26 pm
Well, color me red! Uh, yeah, no need for a loop; for some reason I was thinking double spaces would need it.
duh.
Haveaniceday!
Signature is NULL
August 21, 2003 at 3:38 pm
True, Calvin's and Greg's code is simpler but less flexible.
What if you only wanted to to delete multiple spaces?
i.e.
'Have a nice day!' to
'Have a nice day!'
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 21, 2003 at 3:43 pm
Now why did the multiple spaces get removed?
'Have___a__Nice_day!' to
'Have_a_nice_day!'
When I wrote this originally I was scrubbing some data that had a ton of problems like this. It had been converted by several someone elses before me and they had concatinated a bunch of fields with little to no regard for spacing.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 21, 2003 at 3:47 pm
Well, in this case you WOULD need a loop 🙂
It seem like the extra whitespaces are being stripped out of the HTML. Is that a bug?
declare @String varchar(255)
--The string below has multiple whitespaces
set @String = 'Have a nice day'
While 1 = 1
begin
--this replaces all 2 concurrent whitespaces with 1
set @String = replace(@String, ' ', ' ')
--If no 2 whitespaces together, break
IF charindex(' ', @String) = 0 Break
end
select @String
PS: Nice webpage, Greg. I'll have to play around with that ROLLUP function; I've read about it but never really seen it in practical code.
Signature is NULL
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply