PowerShell: How to create Regex for a repeating pattern

Question

I have below query I need to extract all the column name changes but if I use

$query = ",SELECT A.Title AS Identifier ,A.HOTELNAME AS PROPERTYNAME,A.REGION AS REGION,A.T0STATUS AS STATUS,"

$query -match ",.+ AS (.+)," 

I only get the last matched pattern, The result in captured group is Status but I want Identifier,PROPERTYNAME,REGION and STATUS.

So basically my question is how to use regex to find all matches instead of only one.

Code:

",SELECT A.Title AS Identifier ,A.HOTELNAME AS PROPERTYNAME,A.REGION AS REGION,A.T0STATUS AS STATUS," -match ",.+ AS (.+),"
$matches

Expectation: Identifier PROPERTYNAME REGION STATUS.

Result: STATUS

Also there is not much documentation for PowerShell Regex specifically so can you also let me know which flavor of regex PowerShell uses.

Answer 1

For multiple matches you will need the Select-String cmdlet.
See also: How to capture multiple regex matches, from a single line, into the $matches magic variable in Powershell?

With regards to the regular expression,you probably want to use a Lookahead and Lookbehind

$query |Select-String -AllMatches '(?<=\s+AS\s+)\S+(?=\s*\,)'

$Select = $query |Select-String -AllMatches '(?<=\s+AS\s+)\S+(?=\s*\,)'
$Select.Matches.Value

Identifier
PROPERTYNAME
REGION
STATUS