CODEWITHSUNDEEP
powershell
Me-si
Me-si

Reputation: 72

Powershell : Error: "Input string was not in a correct format."

While assigning the below query to a variable the error

Input string was not in a correct format.

is displayed. I have used similar instances in other scripts which works fine.

 $MyQuery = "   select a.asset_name as 'ASSET_NAME',b.asset_name AS
 'ASSET',
              from positions a 
              join appraisal b
              on b.asset_name LIKE CONCAT("%",a.asset_name,"%") where 
               a.unrealised!= 0 ;"

Upvotes: 0

Views: 15809

Answers (1)

Mark Wragg
Mark Wragg

Reputation: 23355

The issue occurs because your string includes double quotes, which you need to escape, either by entering them twice like this:

$MyQuery = "select a.asset_name as 'ASSET_NAME',b.asset_name AS 'ASSET', from positions a join appraisal b on b.asset_name LIKE CONCAT(""%"",a.asset_name,""%"") where a.unrealised!= 0 ;"

Or by putting a backtick character before them:

$MyQuery = "select a.asset_name as 'ASSET_NAME',b.asset_name AS 'ASSET', from positions a join appraisal b on b.asset_name LIKE CONCAT(`"%`",a.asset_name,`"%`") where a.unrealised!= 0 ;"

Alternatively you can use a here-string, which works by surrounding the string with @' and '@ (or @" and "@) and makes everything between these characters interpreted as part of the string block, regardless of what other characters you include:

$MyQuery = @'
    select a.asset_name as 'ASSET_NAME',b.asset_name AS
   'ASSET',
              from positions a 
              join appraisal b
              on b.asset_name LIKE CONCAT("%",a.asset_name,"%") where 
               a.unrealised!= 0 ;
'@

Note that with a here-string you can't put anything on the same line after the opening @' and the closing '@ must be on a line on it's own, and can't have any whitespace before it.

If you use the double quoted version of the here string, you can still embed variables in the string and have them expanded.

For more information on here-strings see here.

Upvotes: 2

Related Questions