Convert <NULL> to string

• DrStrangepork

  SSC-Addicted

  Points: 460

  More actions

  June 28, 2010 at 12:25 pm

  #387231

  I have a table Orders that contains order information, and I need to produce reports that show the usage of the various input formats for orders. This needs to produce a report something like this:

  | Format | # of Orders of this Format |

  | web | 200 |

  | FTP | 20 |

  | unknown | 10 |

  However, some of the rows in the Orders table will have Format='' (blank) and Format=NULL (a null field). For this report, I need to convert the Format field I query for to 'unknown' in both cases. How can I go about doing this? Bonus points if you can explain how I can produce a CSV ordered by Format in alphabetical order but where the line "Format,# of Orders of this Format" will always be the first line in the CSV regardless of alphabetical order.

• emily-1119612

  SSCarpal Tunnel

  Points: 4166

  More actions

  June 28, 2010 at 12:45 pm

  #1185967

  This is all untested air code since you didn't provide data in a format that is easy to work with.

  http://qa.sqlservercentral.com/articles/Best+Practices/61537/

  1)

  SELECT COALESCE(NULLIF(Format,'')'unknown' )FROM Orders

  2) Maybe something like...

  SELECT

  Field1,

  100 AS CustomSort

  FROM Orders

  UNION

  SELECT

  'Format' AS Field1,

  1 AS CustomSort

  FROM Orders

  ORDER BY CustomSort

  Then you would exclude the CustomSort from your export to csv.

  You could also use CASE in your ORDER BY statement

  You didn't say how you were creating your csv file, in SSIS you would just output the column headers.

  HTH