• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

tsql BCP csv has extra spaces

Hi Experts

I have a sql query  = Select FirstName, '' as MiddleName, '' as Sirname from tnlNames

I dont want to ouput the Middlename, I use the bcp to create a csv from the above query.
The file creates no problem but when I open the file with note pad I get an extra space in middle name.
i.e
DAVID, ,THORN

but im after
DAVID,,THORN

I cant actually see the ASCII value of the space, Im still looking for some software to assist me in that one.
0
MrDavidThorn
Asked:
MrDavidThorn
  • 2
1 Solution
 
MrDavidThornAuthor Commented:
The char value is 20, if that helps anyone
0
 
luckeydogCommented:
SELECT FristName+',,'+Sirname from tnlNames
0
 
MrDavidThornAuthor Commented:
isnt that just creating one field? - its not practical for me.
0
 
Kevin CrossChief Technology OfficerCommented:
If you are sending this to BCP, does it actually matter if you send multiple fields or one field concatenated by comma delimiter already? I think that was the suggestion, but anyway try it with NULL.

bcp "SELECT FirstName, NULL as MiddleName, Sirname FROM tnlNames;" ...

FYI, in your question above, you also have a literal '' for the Sirname, so removed that in example given you are showing that in the final result. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now