Remove spaces

jayfox · Jul 26, 2004

Hi, I'm trying to remove spaces and html tags from a text field. The functions work great when I test them on data I created but when I go to use them on the field where html data is pulled into they don't work. I can't get this function to remove the spaces. In return this function does not remove the spaces.

EX. @desc = This is a test <html>
Function:
CREATE FUNCTION dbo.fnremovespace (@desc varchar(4000))
RETURNS varchar(4000)
BEGIN
while charindex('>',@desc)>0 and substring(@desc,charindex('>',@desc)+1,1) = ' '
begin
set @desc = stuff(@desc,charindex('>',@desc)+1,1,'')
end
return(@desc)
end

This function should return:
 This is a test<html>

john76 · Jul 26, 2004

Take a look at the [red]Replace[/red] function:

Code:

Set @desc = Replace(@desc, ' ', '')
Set @desc = Replace(@desc, '<', '[')
Set @desc = Replace(@desc, '>', ']')

This code should (ie - haven't tested it) remove all spaces and change <> characters to [] characters.

HTH,
John

jayfox · Jul 26, 2004

That would get rid of the spaces but it will also remove the spaces in between the text I'm trying to pull out. When complete @desc = This is a test. If I do a replace it will read Thisisatest

SQLBill · Jul 26, 2004

You could always try:

Code:

REPLACE(@DESC, '>  ', '>')
REPLACE(@DESC, '  <', '<')
REPLACE(@DESC, '> ', '>')
REPLACE(@DESC, ' <', '<')

That should change: This is a test <html>
to This is a test <html>

You have to run it twice since there are two spaces (in your example) between > and This and between test and <. So first you need to eliminate the double spaces and then the single ones.

Once you get rid of the spaces, then eliminate the HTML.

-SQLBill

ESquared · Jul 26, 2004

Just do these repeatedly until no rows are affected, but you'll have to watch out for these character patterns occuring inside strings.

REPLACE(@DESC, '> ', '>')
REPLACE(@DESC, ' <', '<')

-------------------------------------
A sacrifice is harder when no one knows you've made it.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Remove spaces

jayfox

Programmer

john76

MIS

jayfox

Programmer

SQLBill

MIS

ESquared

Programmer

Similar threads

Part and Inventory Search

Sponsor