CODEWITHSUNDEEP
regexpowershell
Peter M.
Peter M.

Reputation: 856

Regex for multiple matches

I am struggling to find a regex that matches all fieldnames in a SQL statement that is assigned to a string variable like this:

sqlText := "Select Nummer,"
sqlText += "Bez,"
sqlText += "Buchstabe,"
sqlText += "Maske,"
sqlText += "Zaehlkarte,"
sqlText += "Verteilart,"
sqlText += "Turnus,"
sqlText += "Verfart "
sqlText += "From AzStamm order by Nummer"

What I have come up with so far is 

Select\s+([A-Za-z]+),

for the pattern.

What I want are all the field names like Nummer,Bez,Buchstabe etc.

I am using PowerShell but it could be C# (or probably Java or PHP) as well:

[Regex]::Matches($sql, $Muster,[System.Text.RegularExpressions.RegexOptions]::Multiline)

I am probably missing something in the pattern (but I assume that the regex is greedy per default).

Regards, Peter

Upvotes: 1

Views: 75

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174700

If the column names are always consisting of only letters, you could use: 

select ((?:\p{L}+,\s*)*\p{L}+) from

... to grab the comma-separated list of names between select and from:

$sqlText  = "Select Nummer,"
$sqlText += "Bez,"
$sqlText += "Buchstabe,"
$sqlText += "Maske,"
$sqlText += "Zaehlkarte,"
$sqlText += "Verteilart,"
$sqlText += "Turnus,"
$sqlText += "Verfart "
$sqlText += "From AzStamm order by Nummer"

if($sqlText -match 'select ((?:\p{L}+,\s*)*\p{L}+) from '){
  $ColumnNames = $Matches[1].Split(",") |ForEach-Object Trim
}
else{
  Write-Error "Couldn't extract column names"
}

$ColumnNames will be an array of column name strings

Upvotes: 1

Theo
Theo

Reputation: 61168

I have no idea what syntax that is, but in PowerShell you would create the $sqText more like 

$sqText = 'Select Nummer,Bez,Buchstabe,Maske,Zaehlkarte,Verteilart,Turnus,Verfart From AzStamm order by Nummer'

Then you can do this:

$fieldnames = $sqlText -replace '^Select\s+(.*)\s+From.*$', '$1' -split ','

to obtain an array with the field names:

Nummer
Bez
Buchstabe
Maske
Zaehlkarte
Verteilart
Turnus
Verfart

Upvotes: 0

Related Questions