Powershell Invoke-Sqlcmd string output

Question

I am querying data from SQL server.

$usersStatus = Invoke-Sqlcmd `
    -query "USE Database 
        SELECT [Status],[ID]
        FROM [tblUsers]" `
    -ServerInstance "Sqlserver\sqlinst" | Select-Object ID,Status

Getting output from query:

foreach ($user in $usersStatus)
{         
    Write-Host "asd"$user.Status"asdad"
}

Output:

asd Queued          asdad
asd Queued          asdad
asd Queued          asdad
asd Queued          asdad

Question:

I would like to kindly ask, if any one know why I always have white space in string please? Do I realy have to use trim() like: Write-Host "asd"$user.Status.trim()"asdad" with every string I am reciving from Invoke-Sqlcmd. What if my project has 2000 lines? Is there nay trick to get string from SQL without White spaces please?

_why I always have white space in string_ Possibly because column `Status` in database table `tblUsers` is padded with trailing spaces. Refer to [SET ANSI_PADDING](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql?view=sql-server-ver15) — Abra, Feb 21 '20 at 13:25
You should be able to TRIM the data with SQL, without impacting the rest of your PowerShell code. Something like: `"SELECT LTRIM(RTRIM([Status])) [Status],[ID] FROM [tblUsers]"`. Hope this helps. — leeharvey1, Feb 21 '20 at 17:52
Note that the space between “asd” and “Queue” is because of the way you’re defining your parameter to write-host. If you use string interpolation instead of your current form these separator spaces will disappear: Write-Host "asd$($user.Status)asdad". (I’m not totally sure but I think your syntax creates an array of 3 values that get written out with spaces between them). In any case, it won’t remove whitespace in your sql data though so you’ll need to apply the comments above for those as well. — mclayton, Feb 23 '20 at 04:27

Powershell Invoke-Sqlcmd string output

0 Answers0