Click here to Skip to main content
14,927,655 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. 
   

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