Substitute member of variable within string in Powershell

Question

I have the following string expression in a PowerShell script:

"select count(*) cnt from ${schema}.${table} where ${col.column_name} is null"

The schema and table resolve to the values of $schema and $table, respectively. However, an empty string is supplied for ${col.column_name}. How can I dot into the member of a variable as part of a string substitution?

Answer 1

How about:

"select count(*) cnt from $schema.$table where $($col.column_name) is null"

Answer 2

One way would be:

"select count(*) cnt from $schema.$table where $($col.column_name) is null"

Another option would be

"select count(*) cnt from {0}.{1} where {2} is null" -f $schema, $table, $col.column_name

Answer 3

I think the problem you're having is mainly syntax related. If you have a variable named $foo, ${foo} references the same variable. So, the ${table} and ${schema} references in your sql string work ok.

The issue is with ${col.column_name}. Your variable (I assume) is called $col, and has a member named column_name. As Robert and Steven both indicate in their answers, to refer to this, you should use $($col.column_name). In general, $(expression) will be replaced with the value of the expression.

The reason for allowing braces in variable names is so that variables can have unusual characters in their names. I would recommend not using the ${} syntax (unless you have a compelling reason), and replacing it with straight $var references for variables and $($var.member) for member references in strings.