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

Question

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 ;"

Mark Wragg · Accepted Answer · 2017-10-26 05:54:42Z

2

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.

answered Oct 26, 2017 at 5:54

Mark Wragg

23.6k7 gold badges48 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Me-si Over a year ago

that did solve the issue . My doubt further is that using similar query syntax in other script files isn't giving errors and is running correctly .

Mark Wragg Over a year ago

Your problem here is that your string contains a mixture of single and doubble quotes. If you have previously had just strings with double quotes in them you can surround them with single quotes and vice versa.

Me-si Over a year ago

This is helpful !!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related