Click here to Skip to main content
14,867,838 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

Thank you in advance.

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

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
Comments
Raghu S Gowda 10-Apr-15 1:28am
   
Thank you got it.
Wendelius 13-Apr-15 23:41pm
   
Glad it helped
Maciej Los 10-Apr-15 2:14am
   
+5!
Wendelius 13-Apr-15 23:41pm
   
Thanks :)
User TRIM Function to remove spaces
TRIM[^]

RTEIM[^]

LTRIM[^]
   
v2
Comments
Maciej Los 10-Apr-15 2:15am
   
King, OP want to remove unwanted spaces between words...
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. 
   
 DECLARE @str varchar(150)
SET @str='Hello    Welcome     to     codeproject.com'
Select REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900