Check for dbnull values in a list of objects

Asked Mar 30 '16 at 13:29

Active Mar 30 '16 at 13:32

Viewed 68 times

I have an object array that I retrieved from the Invoke-SQLCmd. Now I want to replace all the newlines in the AboutPart column.

Here is the code.

$rows = Invoke-SQLCmd -ServerInstance $Server -Database $Database -inputfile "$queriespath\select_WHIProducts.sql" 
foreach($row in $rows) {
    if($row.AboutPart) {
        $row.aboutpart.gettype()
        $row.AboutPart = $row.AboutPart.replace("`r`n",' ')
    }
}

This works, however I am getting errors saying dbnull does not contain a method replace. I've done a check before the replace call and the nulls are still getting through according to the getType() call.

edited Mar 30 '16 at 13:32

Tomalak

332,285
67
532
628

asked Mar 30 '16 at 13:29

Patrick Mahoney

1

You could simply replace the newlines right in the SQL (`select replace(AboutPart, char(13)+char(10), ' ') as AboutPart from TheTable`), this way you would not have to bother in your Powershell script. – Tomalak Mar 30 '16 at 13:33
wow thanks let me try this – Patrick Mahoney Mar 30 '16 at 13:34
1

Alternatively look at the duplicate question, it shows how to deal with dbnull values in Powershell. – Tomalak Mar 30 '16 at 13:35
1

I'd probably just use `SELECT COALESCE(AboutPart, '') AS AboutPart`. Get rid of the NULLs, – Bacon Bits Mar 30 '16 at 14:02
Thank you everyone you have solved my problem!!! – Patrick Mahoney Mar 30 '16 at 19:09

Check for dbnull values in a list of objects

0 Answers0