sql server programming: Hi

I have a address string stored in a varchar column data type. Sometime there spacing between two words in a string is greater than a single space. How do I delete the extra spaces in the string. I have a parser which is failing because of the extra spaces

Here is an example that uses multiple REPLACE statements to remove white
space:

http://www.geocities.com/sqlserverexamples/string2.htm

--

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----

Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
[quoted text, click to view]
there spacing between two words in a string is greater than a single space.
How do I delete the extra spaces in the string. I have a parser which is
failing because of the extra spaces.
[quoted text, click to view]

Razi
Try
declare @sql varchar(50)
set @sql='aaaa '
select datalength(@sql)
select @sql=ltrim(rtrim(@sql))
select datalength(@sql)


[quoted text, click to view]
there spacing between two words in a string is greater than a single space.
How do I delete the extra spaces in the string. I have a parser which is
failing because of the extra spaces.
[quoted text, click to view]

Look up the REPLACE() function and write a series of nested calls to it,
then use the LTRIM() and RTRIM () that. Do not use procedural as others
have advised you!

RTRIM (LTRIM (REPLACE (REPLACE ( ...))

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

DECLARE @str varchar(64)
SET @str = 'This stiring contains too many spaces!'
PRINT @str
WHILE (ISNULL(CHARINDEX(' ', @str), 0) > 0) BEGIN
SET @str = REPLACE(@str, ' ', ' ')
PRINT @str
END

You can try this sample using UDF


Create FUNCTION remove_extra_space
(@str varchar(8000))
RETURNS varchar(8000)
BEGIN
declare @clean_text varchar(8000)
Set @Clean_text=@str
while patindex('% %',@clean_text ) >0 Set
@clean_text=replace(@clean_text,' ',' ')

Set @Clean_text=ltrim(rtrim(@Clean_text))
return @clean_text
end

declare @teststring varchar(8000)
Set @testString=' This is test string '
select dbo.remove_extra_space(@teststring)


[quoted text, click to view]
there spacing between two words in a string is greater than a single space.
How do I delete the extra spaces in the string. I have a parser which is
failing because of the extra spaces.
[quoted text, click to view]

If you want to delete the exta spaces inbetween words, there is no standard
function for the same. You will need to iterate thorugh each letter of the
string and then form a new string with only a single space between the
words.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

[quoted text, click to view]
there spacing between two words in a string is greater than a single space.
How do I delete the extra spaces in the string. I have a parser which is
failing because of the extra spaces.
[quoted text, click to view]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_76lh.asp

--
Jeff Duncan
MCDBA, MCSE+I
[quoted text, click to view]
there spacing between two words in a string is greater than a single space.
How do I delete the extra spaces in the string. I have a parser which is
failing because of the extra spaces.
[quoted text, click to view]

[quoted text, click to view]
words in a string is greater than a single space. How do I delete the extra spaces in the string. I
have a parser which is failing because of the extra spaces.
[quoted text, click to view]

This question seems to come up regularly. A few months back I posted
a stored procedure that will return an optimal sequences of lengths of strings
of spaces that can be passed to the REPLACE function to always remove all
extra spaces from an input string of given length. See http://tinyurl.com/29aez.
For example, say you have a VARCHAR(50), then run

EXEC InsertDivisorSequences 50

which will compute all space sequences of shortest length that can handle an
input string of max length 50. To view the results

SELECT divisor_sequence, number_of_divisors, max_dividend
FROM DivisorSequences
WHERE max_dividend >= 50
ORDER BY number_of_divisors, divisor_sequence

and you'll see over 1200 possibilities of length 5, e.g., 10.10.3.2.2 means
that 5 consecutive calls to REPLACE with strings of spaces of the specified
lengths will always remove all excess spaces from a VARCHAR(50). More
than 5 calls to REPLACE are never necessary for a VARCHAR(50). For a
maximum-length VARCHAR, size 8000, the shortest sequences are of length
6, that is, more than 6 calls to REPLACE are never needed. And you have
over 224,000 possibilities to choose from!

DECLARE @spaces10 VARCHAR(10)
DECLARE @spaces3 VARCHAR(3)
DECLARE @spaces2 VARCHAR(2)
DECLARE @spaces1 VARCHAR(1)
DECLARE @input VARCHAR(50)
DECLARE @output VARCHAR(50)

SET @spaces10 = REPLICATE(' ', 10)
SET @spaces3 = REPLICATE(' ', 3)
SET @spaces2 = REPLICATE(' ', 2)
SET @spaces1 = ' '
SET @input = 'This is a test'

SET @output =
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(@input, @spaces10, @spaces1),
@spaces10, @spaces1),
@spaces3, @spaces1),
@spaces2, @spaces1),
@spaces2, @spaces1)

SELECT @input AS input, @output AS output

input output
This is a test This is a test

--
JAG

Thanks Radovan