Remove all spaces from string in SQL Server

Last Reply 9 months ago By pandeyism

Posted 9 months ago

Hi All,

I have created below query.

 I have two tables EmpDetails and EmpData. I am inserting data into EmpDetails from Empdata.

After copying data row resulted from above query and pasted into excel it creates blank space row.

Is there any way while inserting data from select query is should ignore extra hidden line space. 

Please advice.. 

Declare @EmpID nvarchar(50)
Declare @Status_ID_FK int
Declare @Dcode int


set @EmpID ='10001'
set @Status_ID_FK =1
set @Dcode= select Dcode from STG2011.[dbo].[vw_empview] where EmpNumer=@EmpID	)

Insert into CNS2015.[dbo].EmpDetails (EmpName,EmpAddress,EmpMobile,EmpGrade,Dcode)

select EmpName,EmpAddress,EmpMobile,EmpGrade,@Dcode from STG2011.[dbo].EmpData where EmpId=@EmpID
Posted 9 months ago

Hi Waghmare,

Please refer below query for hide extra spaces.

SQL

DECLARE @EmpData AS TABLE(EmpId VARCHAR(10),EmpName VARCHAR(200),EmpAddress VARCHAR(20),EmpMobile VARCHAR(10),EmpGrade VARCHAR(5),Dcode  INT)
INSERT INTO @EmpData VALUES('10001','test             Name','malad','1464654','A',1)
SELECT * FROM @EmpData
SELECT REPLACE(REPLACE(REPLACE(EmpName,' ','<>'),'><',''),'<>',' ')EmpName,EmpAddress,EmpMobile,EmpGrade,Dcode FROM @EmpData

Screenshot