14,870,204 members
See more:
hi,
i'm fetching the data from the database by adding the 2 columns as a single column bt in the fetched column there are lot of spaces between the words so how to remove the spaces except a single space,

here is how my query look like:
SQL
`select Pers_FirstName + Pers_LastName as Name from Person inner join PhoneLink on Pers_PersonId=PLink_RecordID inner join Phone on Phon_PhoneId=PLink_PhoneId and Phon_Number='6983000';`

output:
Name
Peter Jackson

Edited:typo
Posted
Updated 16-Dec-20 16:56pm
v3

## Solution 5

``` DECLARE @str varchar(150)
SET @str='Hello    Welcome     to     codeproject.com'
Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')```

## Solution 3

Solution 2 provided by Mika Wendelius[^] is very good. I'd propose to use CTE[^]:

SQL
```DECLARE @tmp TABLE(MyText VARCHAR(500))

INSERT INTO @tmp (MyText)
VALUES('This    is      very  strange      text...'),
('This    is   another   very  strange      text.'),
('This    is      very  stupid      text   !!!')

DECLARE @words TABLE(RowNo INT, PartNo INT, MyWord VARCHAR(255))

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY MyText) AS RowNo, 1 AS PartNo, LEFT(LTRIM(MyText), CHARINDEX(' ', LTRIM(MyText))-1) AS MyWord, RIGHT(LTRIM(MyText), LEN(LTRIM(MyText)) - CHARINDEX(' ', LTRIM(MyText))) AS Remainder
FROM @tmp
WHERE CHARINDEX(' ', LTRIM(MyText))>0
UNION ALL
SELECT RowNo, PartNo + 1 AS PartNo, LEFT(LTRIM(Remainder), CHARINDEX(' ', LTRIM(Remainder))-1) AS MyWord, RIGHT(LTRIM(Remainder), LEN(LTRIM(Remainder)) - CHARINDEX(' ', LTRIM(Remainder))) AS Remainder
FROM CTE
WHERE CHARINDEX(' ', LTRIM(Remainder))>0
UNION ALL
SELECT RowNo, PartNo + 1 AS PartNo, LTRIM(Remainder) AS MyWord, NULL AS Remainder
FROM CTE
WHERE CHARINDEX(' ', LTRIM(Remainder))=0
)
INSERT INTO @words (RowNo, PartNo, MyWord)
SELECT RowNo, PartNo, RTRIM(LTRIM(MyWord)) AS MyWord
FROM CTE
ORDER BY RowNo, PartNo

--SELECT *
--FROM @words
--ORDER BY RowNo, PartNo

SELECT DISTINCT t2.RowNo,
(
SELECT t1.MyWord + ' ' AS [text()]
From @words AS t1
Where t1.RowNo = t2.RowNo
ORDER BY t1.PartNo
For XML PATH ('')
) AS MySentence
From @words AS t2```

Result:
```RowNo	MySentence
1	    This is very strange text...
2	    This is very stupid text !!!
3	    This is another very strange text. ```

## Solution 2

To add to King Fisher's answer, if the multiple spaces are inside a single text column and/or you want a single space to remain then use REPLACE[^] function. Consider the following examples

SQL
```SELECT REPLACE('A B'  , '  ', ' ');
SELECT REPLACE('A  B' , '  ', ' ');;```

The problem is that if you have more than two spaces then REPLACE would replace dual spaces only once. For this you can either build a small UDF or you can repeat the REPLACE as many times as needed. For example
SQL
`SELECT REPLACE( REPLACE( REPLACE('A     B', '    ', ' '), '   ', ' '), '  ', ' ');`
v4
Raghu S Gowda 10-Apr-15 1:28am

Thank you got it.
Wendelius 13-Apr-15 23:41pm

Maciej Los 10-Apr-15 2:14am

+5!
Wendelius 13-Apr-15 23:41pm

Thanks :)

## Solution 1

User `TRIM `Function to remove spaces
TRIM[^]

RTEIM[^]

LTRIM[^]
v2
Maciej Los 10-Apr-15 2:15am

King, OP want to remove unwanted spaces between words...