Use findstr to replace null values with empty strings from sqlcmd command

Question

I have an sqlcmd command which generates a csv file from a view. Is it possible to replace the NULL values to empty string using the findstr command?

Here is what I tried.

sqlcmd -S . -d SAMPLEDB -U sa -P pass -s"|" -W -Q "SET NOCOUNT ON SET ANSI_WARNINGS OFF select * from view_Table" > Sample.csv -h -1 | findstr /v /c:"NULL"

Answer 1

You can easily build the extraction SQL for each view using the system management views. This simple query:

SELECT v.[name]
      ,c.[name]
      ,c.[column_id]
      ,c.[is_nullable]
FROM sys.views V
INNER JOIN sys.columns C
    ON V.[object_id] = C.[object_id];

will return everything we need to perform the task:

• the view name
• the column name
• the column order
• if the column is nullable

So, we need only to build the extraction SQL statements:

SELECT v.[name]
      ,'SELECT ' + DS.[definition] + ' FROM ' + v.[name]
FROM sys.views V
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT ',' + CASE WHEN c.[is_nullable] = 1 THEN 'ISNULL(' + c.[name] + ','''')' ELSE c.[name] END
            FROM sys.columns C
            WHERE V.[object_id] = C.[object_id]
            ORDER BY c.[column_id]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) DS ([definition]);

Depending on your SQL version you can reduce the code above - for example using IIF or STRING_AGG.

Also, you can add WHERE clause to filter the query for specific views.