?
Solved

Remove spaces between select statements

Posted on 2013-01-25
16
Medium Priority
?
411 Views
Last Modified: 2013-01-28
I have a MSSQL Query That returns a file that I export as a .udf file that can be imported into my food service software but I get a break where I don't want one and can not get rid of the space without editing the file.

Here is the Query
SET NOCOUNT ON
 SELECT '!Version: 4.2' +CHAR(13)+
'!RecordCount:'+
cast((SELECT count(*)
FROM         STUDENT_MASTER INNER JOIN
                      NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE     (STUDENT_MASTER.ROOM_TYPE = '1s') OR
                      (STUDENT_MASTER.ROOM_TYPE = '2s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '3s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '4s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '5s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '6s')
                      ) as varchar)
 +CHAR(13)+
'!BeginFormat' +CHAR(13)+
 '!Odyssey_PCS,' + '      ' + '1,' + '      ' + '6,' + '      ' + '"IDNUMBER"' +CHAR(13)+
'!Odyssey_PCS,' +  '      ' + '2,'+ '      ' + '6,'+ '      ' + '"CARDNUMBER"' +CHAR(13)+
'!Odyssey_PCS,' + '      ' + '14,' + '      ' + '20,' + '      ' + '"LASTNAME"' +CHAR(13)+
'!Odyssey_PCS,' + '      ' + '34,' + '      ' + '21,' + '      ' + '"FIRSTNAME"' +CHAR(13)+
'!EndFormat'+CHAR(13)+
'!BeginData ' 

SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
FROM         STUDENT_MASTER INNER JOIN
                      NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE     (STUDENT_MASTER.ROOM_TYPE = '1s') OR
                      (STUDENT_MASTER.ROOM_TYPE = '2s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '3s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '4s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '5s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '6s')
ORDER BY NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME

SELECT '!EndData' AS ' '

Open in new window

!Version: 4.2
!RecordCount:162
!BeginFormat
!Odyssey_PCS,      1,      6,      "IDNUMBER"
!Odyssey_PCS,      2,      6,      "CARDNUMBER"
!Odyssey_PCS,      14,      20,      "LASTNAME"
!Odyssey_PCS,      34,      21,      "FIRSTNAME"
!EndFormat
!BeginData

107951      Test                      Student1
110004      Test                      Student2
108332      Test                      Student3

!EndData

I need the break between !BeginData and Test Student1 gone as well as
the break between Test Student 3 and !EndData

Any help here would be greatly appreciated!
0
Comment
Question by:CCC-Ravens
  • 8
  • 7
16 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38821168
can you post in what exact format you need to have output based on the above posted example

you can use for formatting output : line ending characters; Carriage Return ( CR - CHAR(13) ) and Line Feed (LF - CHAR(10) ).
also Replace sql function to remove "extra" space chars
http://msdn.microsoft.com/en-us/library/ms186862(v=sql.105).aspx

Control character

Value


Tab
 
char(9)
 

Line feed
 
char(10)
 

Carriage return
 
char(13)
0
 
LVL 6

Expert Comment

by:liija
ID: 38822651
Extra line end is added after every query.
You can get around this using combination of command line tools osql and findstr. Print the results to file with osql and remove the line endings with findstr.

Example:
osql -E -d Yourdb -iYourQuery.sql -h-1 -w500 -n -b | findstr /V /r /c:"^$"
 >output.txt
0
 

Author Comment

by:CCC-Ravens
ID: 38827218
OUT PUT SHOULD BE.....

___________________________________________________________________________________________
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS,      1,      6,      "IDNUMBER"
!Odyssey_PCS,      2,      6,      "CARDNUMBER"
!Odyssey_PCS,      14,      20,      "LASTNAME"
!Odyssey_PCS,      34,      21,      "FIRSTNAME"
!EndFormat
!BeginData
999999           Test          Student1
888888           Test          Student2
777777           Test          Student3
!EndData
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 6

Expert Comment

by:liija
ID: 38827435
My script above does the trick. Check that.
0
 

Author Comment

by:CCC-Ravens
ID: 38827572
Your cmd seems to work but it is not creating the output file for me.
0
 

Author Comment

by:CCC-Ravens
ID: 38827653
Ok fixed the output problem, But it is still not quite right. it is showing up as...
______________________________________________________________________________________

 !Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS,      1,      6,      "IDNUMBER"
!Odyssey_PCS,      2,      6,      "CARDNUMBER"
!Odyssey_PCS,      14,      20,      "LASTNAME"
!Odyssey_PCS,      34,      21,      "FIRSTNAME"
!EndFormat
!BeginData                            
      104006 Test                         Student1        
      109746 TEST                         Student2
      110976 Test                          Student3
!EndData

The space at the beginning has to go away and the dead space before  each student cannot be there.

 <--!Version: 4.2
!RecordCount:352
!BeginFormat
!Odyssey_PCS,      1,      6,      "IDNUMBER"
!Odyssey_PCS,      2,      6,      "CARDNUMBER"
!Odyssey_PCS,      14,      20,      "LASTNAME"
!Odyssey_PCS,      34,      21,      "FIRSTNAME"
!EndFormat
!BeginData                            
<----104006 Test                         Student1        
<----109746 TEST                         Student2
<----110976 Test                          Student3
!EndData
0
 
LVL 6

Expert Comment

by:liija
ID: 38827656
Hmm, works for me, creates the output.txt defined at the end of the script.
0
 

Author Comment

by:CCC-Ravens
ID: 38827684
I got it to create the file but the format is just not quite correct. It has to be exact.

it must be exactly...

________________________________________________________________________________________
!Version: 4.2
!RecordCount:343
!BeginFormat
!Odyssey_PCS,      1,      6,      "IDNUMBER"
!Odyssey_PCS,      2,      6,      "CARDNUMBER"
!Odyssey_PCS,      14,      20,      "LASTNAME"
!Odyssey_PCS,      34,      21,      "FIRSTNAME"
!EndFormat
!BeginData
104006      Test                         Student1
109746      Test                         Student2
110976      Test                         Student3
!EndData
0
 
LVL 6

Expert Comment

by:liija
ID: 38827868
You should wrap this part into one string:
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
->

SELECT
 CAST(STUDENT_MASTER_ID_NUM AS CHAR(10)) + CAST(NAME_MASTER.LAST_NAME AS CHAR(15))+ CAST(NAME_MASTER.FIRST_NAME AS CHAR(20))

With correct CHAR lenghts you should be able to align the columns?
0
 

Author Comment

by:CCC-Ravens
ID: 38827947
I am sorry but you lost me on that last post.
0
 
LVL 6

Expert Comment

by:liija
ID: 38827994
I mean that the problem is now in this part of the code:
SELECT STUDENT_MASTER.ID_NUM, NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME
FROM         STUDENT_MASTER INNER JOIN
                      NAME_MASTER ON STUDENT_MASTER.ID_NUM = NAME_MASTER.ID_NUM
WHERE     (STUDENT_MASTER.ROOM_TYPE = '1s') OR
                      (STUDENT_MASTER.ROOM_TYPE = '2s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '3s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '4s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '5s,') OR
                      (STUDENT_MASTER.ROOM_TYPE = '6s')
ORDER BY NAME_MASTER.LAST_NAME, NAME_MASTER.FIRST_NAME

SELECT '!EndData' AS ' '

Open in new window


So you should replace the columns with one string where you are able to align them correctly (I presume the 'data' columns should be fixed width?)
In that case the SELECT part should be replaced with something like this - where you are able to align the columns exactly.
SELECT
 CAST(STUDENT_MASTER_ID_NUM AS CHAR(10)) + 
CAST(NAME_MASTER.LAST_NAME AS CHAR(15))+ 
CAST(NAME_MASTER.FIRST_NAME AS CHAR(20))

Open in new window

0
 

Author Comment

by:CCC-Ravens
ID: 38828064
I have tried this as well but the leading spaces are still there
0
 
LVL 6

Expert Comment

by:liija
ID: 38828096
Strange, I cannot reproduce.
ID_NUM is int - not char? And doesn't contain spaces?
LTRIM(CAST(STUDENT_MASTER.ID_NUM AS CHAR(10)) would fix that
0
 

Author Comment

by:CCC-Ravens
ID: 38828122
Yeah and it's weird because the spaces are not there when I run the SQL in SQL Server management studio. But the line breaks are there. Your script gets rid of the line breaks but adds the spaces.
0
 
LVL 6

Accepted Solution

by:
liija earned 2000 total points
ID: 38828139
You could also try to specify column separator for osql to be empty:
-s""
because the default is space
0
 

Author Closing Comment

by:CCC-Ravens
ID: 38828200
Perfect That did the trick.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question