We help IT Professionals succeed at work.

Displaying Blank Fields with Empty Pipes

I have an output to a flat file from the below query.
SELECT  TITLE_UNIT AS BOOK_TYPE
         ,ITEM AS SKU
         ,'' AS Title_Date
         ,'' AS 'BOOK SKU'
         ,TITLE_DESC AS 'Description'
         ,'' AS 'Title Cost'
         ,NULL AS 'Title Costa'
         ,NULL AS 'Title Costb'
         ,'' AS 'Title Costc'
         ,'' AS 'Book Dept'
  INTO Temptbl
FROM BookINVENTORY
WHERE Genre = 'Fiction

 The delimiter is a pipe [|] I need the NULL values (or blank fields) to display with empty pipes
like this:  
The Self Help Guru|7.28||||||||

I am using an SSIS package to output the file.  How do I display the empty pipes?  This is  a customer requirement.
Comment
Watch Question

Top Expert 2011

Commented:
If I'm reading this right, you are selecting everything into a temp table which you are then going to export to the flat-file.  You should be able to specify text to add into your output using something like the following.

SELECT 
  field1 & "|",
  field2 & "|",
  ...
  fieldn
FROM Temptbl

Open in new window

Author

Commented:
Nap0leon,

I get this message when I try your suggestion:

Invalid column name '|'.
Top Expert 2011

Commented:
Too much web-coding... I get my syntaxes mixed up.  To get text added to your query result, it might just be comma delimited...

SELECT 
  field1,"|",
  field2,"|",
  ...
  fieldn
FROM Temptbl

Open in new window

Author

Commented:
Same error message...
Top Expert 2011
Commented:
If you are using an SSIS tool, you should be able to set the delimiter in the tool (tabs, commas, pipes, etc.).  It's been awhile since I tried doing this part manually...

(If the below does not work (using +s), try either of the above but with single-quotes instead of double-quotes).  It is some combination of these... I just don't have a SQL Server handy to try it with.

SELECT 
  field1 + '|',
  field2 + '|',
  ...
  fieldn
FROM Temptbl

Open in new window


Author

Commented:
I have set the delimiter via the ssis packge; but, if the field is blank, it does dispay an empty pipe as in my first post.  The customer requires an empty pipe be dispplayed if the field is blank or null.
Top Expert 2011

Commented:
Ah sorry - I misunderstood your question.

In your example, it is returning only:
The Self Help Guru|7.28|
instead of the desired:
The Self Help Guru|7.28||||||||
?
This sounds like something that would be addressed inside your particular SSIS Tool and not at an SQL level.
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
don't use NULL, but '' for "empty" fields.
NULL will be concatenated to NULL with all other fields. ..

Author

Commented:
Nap0lean,

You are correct, in fact, I was considering closing out this question (because I did get one of your suggestions to work) and re-wording my question so it is SSIS package specific.

Angelll,

I tried passing the fields as empty but my output was still The Self Help Guru|7.28| instead of The Self Help Guru|7.28||||||||

Explore More ContentExplore courses, solutions, and other research materials related to this topic.