Removing spaces in Varchar 2000 field

I have a data field defined as Varchar 2000.
I need to remove all spaces greater than 2 in length from the inside of the data. I can remove beginning and ending spaces.

Data Example:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Need this:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Can anyone help?The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.|||Originally posted by mikeinoxford
The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.

Let say 1=space

select replace('qw11er11tt','11 ','1')|||I will test that. I'm currently working on a process using 'REPLACE'|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.|||I have the same problem|||And the key here is that you want to retain 1 space between each "word".

Yes?|||This probably isn't perfect, but it works. Note that the last (2) replace occurs twice, that is important.DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.cDemo VARCHAR(128)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)
, @.cDemo = 'This is a test of the ability to'
+ ' remove spaces.'

SELECT Replace(Replace(Replace(Replace(Replace(@.cDemo, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1)-PatP|||I'm stuck...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy dog'
GO

SELECT REPLACE(REPLACE(Col1,SPACE(2),'@.'),'@. ',' ') AS Col1 FROM myTable99
GO|||And one more

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'
GO

DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.c32 VARCHAR(20)
, @.c64 VARCHAR(20)
, @.c128 VARCHAR(20)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)

SELECT Replace(Replace(Replace(Replace(Replace(Replace(Co l1, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1), @.c2, @.c1) FROM myTable99
GO

There's gotta be a better way...|||There's gotta be a better way...I don't know of any better way using pure Transact-SQL. While it is ugly, this really is pretty fast and not obnoxiously difficult to code.

-PatP|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.

So if there are three consecutive spaces, do you want them ALL removed, or did you want to leave one space as a separator?|||...and is this a one type deal or part of a repeatable process? Are you looking at actually modifying the underlying data, or just removing the spaces for display. 'Cause its not clear here even what type of code is appropriate; View, Stored Procedure, User-defined function?

Here is one method that cleans all but one intervening space from the data in the table:

SET NOCOUNT ON
CREATE TABLE #myTable99(Col1 varchar(8000))
GO

INSERT INTO #myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog'
UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'

update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

while @.@.RowCount > 0
update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

select * from #myTable99

drop table #myTable99|||I steel have problem

I have table with nvarchar and numbers in it.
It's imposible to convert it to int so I was thinking about spaces behand the number bat the resoliution you showed dosn't works in this case.

So I'm thinking about function (maybe something els) which will take only my numbers which i can convert to int.

Do you have any idea|||I import the table from XLS. and in XLS it looks like space behind the number.|||Not the same problem. Try using RTRIM to get rid of trailing blanks and/or LTRIM to get rid of leading ones.|||That's a different problem than the one that I tried to solve earlier, even though the first problem was amusing!

I'd use something like the following, after you modify it to suit your schema and data:SELECT *
FROM myTable
WHERE 0 = IsNumeric(myColumn)-PatP|||Anyone want to post some sample DDL and DML and expected results like we did...

My telepathic infrared port is not working right now...

My Guess...you got one column that is space delimited with numbers..yes?

You need a UDF to parse it out then strip the numbers and place them in their own columns in another table...|||EDIT: And you should really start a new thread instead of hichjacking this one...|||Absolutely Rafala. Do NOT tag your issue onto the end of this thread. It is WAY too confusing trying to keep track of which problem to solve.|||Would the looping on this be too much?

declare @.str varchar(100)

set @.str = 'this is a test of some code'

select @.str

while charindex (' ', @.str) > 0
begin
select @.str = replace (@.str, ' ', ' ')
end

select @.str

Can't really apply that to a column, though...|||Don't know what this is like perormance wise (performance hit of recusive function calling and both LEN function calls)...but it will stop once it finds no more spaces to replace

CREATE FUNCTION dbo.RecursiveReplace(@.TheString VARCHAR(2000)) RETURNS VARCHAR(2000) AS
BEGIN
DECLARE @.Result VARCHAR(2000);

SET @.Result = REPLACE(@.TheString, ' ', ' ');

IF LEN(@.Result) <> LEN(@.TheString)
SET @.Result = dbo.RecursiveReplace(@.Result);

RETURN @.Result;
END|||I have figured a problem with REPLACE function.

"the space" I have copied from XLS file.|||I mean solved the problem.