Powershell finalizing the end of an array.

Zack used Ask the Experts™
Hi EE,

I have the following code:

$constr = "User Id=$userId;Password=$password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host2)(PORT=$port))(CONNECT_DATA=(SERVICE_NAME=$serviceName)))"

$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$sql="select ph.extension Extension, to_char(elh.beg_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') StartDate 
,to_char(elh.end_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') EndDate 
from encntr_loc_hist elh, phone ph
WHERE elh.encntr_id = 32866515
and   elh.loc_facility_cd = 11664
and   elh.active_ind=1
and   elh.loc_bed_cd = ph.parent_entity_id 
and   ph.extension(+) is not null 
and   ph.parent_entity_name(+) = 'LOCATION'
and   ph.phone_type_cd(+) = 160 
and   ph.end_effective_dt_tm(+) >= elh.end_effective_dt_tm 
and   ph.beg_effective_dt_tm(+) <= elh.beg_effective_dt_tm"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)

$Results = @()
While ($reader.Read())
	Write-Host 'Reading from reader1' -fore Yellow
    Write-Host $temp    	
    $temp = ''
	$temp += $( $reader.GetString(0)) + ', ' 
	$temp += $( $reader.GetString(1)) + ', '
	$temp += $($reader.GetString(2))
    $Results += $temp
Write-Host "Results: $Results"

$phextension = ''
$Results | ForEach-Object {
	$phextension += $_.Split(',')[0] + ','

Write-Host "phextension: $phextension"

$sql2 = "select encntr_id 
from encntr_alias ea 
WHERE ea.alias IN ($([string]$phextension))
and ea.alias_pool_cd = 11"

Write-Host "phextension: $sql2"

$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql2,$conn)
$reader2 = $command.ExecuteReader()

$QueryResult2 = ''

while($reader2.Read()) {
	Write-Host 'Reading from reader2' -fore Green
	$QueryResult2 +=  $reader2.GetString(0)

Write-Host $QueryResult2


Open in new window

The code results in the following error see attached.

The error is caused the last value of the phone extension field having a ',' in it. How do I determine what the final value is in the array I am concatenating into the SQL string I am parsing so I can remove the last '.'

Any assistance is appreciated.

Thank you.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Don't concatenate yourself; that's what the -join operator is for.
Replace lines 38-48 in your script above with the following; it will create an array with the extracted phone extensions, and then just join it:
$phextension = ($Results | ForEach-Object {
}) -join ','

Write-Host "phextension: $phextension"

$sql2 = "select encntr_id 
from encntr_alias ea 
WHERE ea.alias IN ($($phextension))
and ea.alias_pool_cd = 11"

Open in new window

ZackGeneral IT Goto Guy


Hi oBda,

Thank you for that an additional query if I may if I wanted to put quotes around each of the numbers in the array e.g. '6747' is there a command for that?

Chris DentPowerShell Developer
Top Expert 2010
You just need a small tweak to oBdA's example.
$phextension = ($Results | ForEach-Object {
}) -join "','"
$phextension = "'" + $phextension + "'"

Open in new window

The join operator has been tweaked to join on on a comma surrounded by quotes. All that's left is to add a quote to be beginning and the end of the joined string (which is accomplished by the simple concatenation at the end).
ZackGeneral IT Goto Guy


Thank you very much for your assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial