Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: 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 place

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    set @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 ''
    -bpd

  3. #3
    Join Date
    Jan 2004
    Posts
    10
    Thanks a lot. It works just great.

    Thank You

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •