CODEWITHSUNDEEP
sqlarrayspowershellrows
Daeny
Daeny

Reputation: 23

Getting specific rows in a Powershell variable/array

I hope I'm able to ask my question as simple as possible. I am very new to working with PowerShell.

Now to my question:

I use Invoke-Sqlcmd to run a query, which puts Data in a variable, let's say $Data.

In this case I query for triggers in an SQL Database.

Then I kind of split the array to get more specific information:

$Data2 = $Data | Where {$_.table -like 'dbo.sportswear'}
$Data3 = $Data2 | Where {$_.event -match "Delete"}

So in the end I have a variable with these Indexes(?), I'm not sure if they are called indexes.

table
trigger_name
activation
event
type
status
definition

Now all I want is to check something in the definition. So I create a $Data4 = $Data3.definition, so far so good.

But now I have a big text and I want only the content of 2-3 specific rows. When I used like $Data4[1] or $Data4[1..100], I realized that PowerShell sees every char as a line/row.

But when I just write $Data4 it shows me the content nice formatted with paragraphs, new lines and so on.

Has anyone an idea how I can get specific rows or lines of my variable?

Thank you all :)

Upvotes: 2

Views: 2430

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25001

It appears $Data4 is a formatted string. Since it is a single string, any indexed element lookups return single characters (of type System.Char). If you want indexes to return longer substrings, you will need to split your string into multiple strings somehow or come up with a more sophisticated search mechanism.

If we assume the rows you are after are actual lines separated by line feed and/or carriage return, you can just split on those newline characters and use indexes to access your lines:

# Array indexing starts at 0 for line 1. So [1] is line 2.
# Outputs lines 2,3,4
($Data4 -split '\r?\n')[1..3]

# Outputs lines 2,7,20
($Data4 -split '\r?\n')[1,6,19]

-split uses regex to match characters and perform a string split on all matches. It results in an array of substrings. \r matches a carriage return. \n matches a line feed. ? matches 0 or one character, which is needed in case there are no carriage returns preceding your line feeds.

Upvotes: 1

Related Questions