an unwanted line - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

an unwanted line

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-19 : 10:31:51

Hi all,
When I use ECHO command the file insert a line at the end of file, but I do not want a line at end of file.
See:
I want to show the result after concatenating in just one line/row



--Create Text File
DECLARE @list VARCHAR(1000) = 'AROUT, ANTON, TOMSP',
        @command VARCHAR(1000);
SET @command = 'echo ' + @list + '>>c:\test.txt';
EXECUTE xp_cmdshell 'del c:\test.txt';
EXECUTE xp_cmdshell @command
GO

--Createing Temporary Table
IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp
(list VARCHAR(1000));
GO

--Bulk Inserting
BULK INSERT #Temp FROM 'c:\test.txt'
WITH
(ROWTERMINATOR = ', ');
GO


--Assignment 
DECLARE @list VARCHAR(1000) = ''
SELECT @list = @list + list + ', '
  FROM #Temp 
 ORDER BY list ASC
 
 select @list
/*
------------------------
ANTON, AROUT, TOMSP
, 
*/

But I want see the result similar this:

-----------------------
ANTON, AROUT, TOMSP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 10:41:57

select substring(@list,1,len(@list)-1)

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-19 : 11:12:30

See, I publish three rows in a table then I will want diference of two table with same rows but the result is not correct (result must be empty set!). please help me.

--Create Text File
DECLARE @list VARCHAR(1000) = 'AROUT, ANTON, TOMSP',
        @command VARCHAR(1000);
SET @command = 'echo ' + @list + '>>c:\test.txt';
EXECUTE xp_cmdshell 'del c:\test.txt';
EXECUTE xp_cmdshell @command
GO

--Createing Temporary Table
IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp
(list VARCHAR(1000));
GO

--Bulk Inserting
BULK INSERT #Temp FROM 'c:\test.txt'
WITH
(ROWTERMINATOR = ', ');
GO


--Assignment 
DECLARE @list VARCHAR(1000) = ''
SELECT @list = @list + list + ', '
  FROM #Temp 
 ORDER BY list ASC
 
 (SELECT 'AROUT' UNION
  SELECT 'ANTON' UNION 
  SELECT 'TOMSP')
  EXCEPT
  SELECT List FROM #Temp  /*
  ------------------
TOMSP

(1 row(s) affected)
*/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 12:02:23

You have extra line in the file

Try this code

(SELECT 'AROUT' UNION
SELECT 'ANTON' UNION
SELECT 'TOMSP')
EXCEPT
SELECT replace(replace(List,char(10),''),char(13),'') FROM #Temp

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-19 : 12:42:46

Thanks a million!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 01:22:25

quote:
Originally posted by ms65g

Thanks a million!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources