2012年3月11日星期日

BCP help -Null values in select

I am using a bcp command to cretae a text file.

set @.bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

exec master..xp_cmdshell @.bcpCommand

Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds placeset @.bcpcommand = 'bcp "select COALESCE(med,'') + replicate('' '',10-datalength(COALESCE(med,''))),COALESCE(ml,'') + replicate('' '',20-datalength(COALESCE(ml,''))),COALESCE(iname,'') + replicate('' '',10-datalength(COALESCE(iname,''))) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

This will replace any NULLs with ''|||Thanks a lot. It works just great.

Thank You

没有评论:

发表评论