Re: How can i get the "true" name of an aliased field?

Bob Barrows [MVP] schrieb:
ekkehard.horner wrote:
Quick and dirty example:

Dim aTests : aTests = Array( _
"SELECT FieldName AS FieldNameAlias FROM table" _
, "SELECT [FieldName] AS [FieldNameAlias] FROM table" _
, "SELECT WhatEver.FieldName AS [FieldNameAlias] FROM table" _
, "SELECT F1 AS A1, F2 AS A2 FROM table" _
, "SELECT F1 AS [AS], F2 AS A2 FROM table" _
)
Dim oRE : Set oRE = New Regexp
oRE.Pattern = "\[?(\w+(?:\.\w+)*)\]?\s+AS\s+\[?(\w+)\]?"
oRE.Global = True
oRE.IgnoreCase = True
Dim sTest, oMTS, oMT
For Each sTest In aTests
WScript.Echo "------", sTest
Set oMTS = oRE.Execute( sTest )
For Each oMT In oMTS
WScript.Echo "", oMT.SubMatches( 1 ), "==>",
oMT.SubMatches( 0 ) Next
Next


Does this handle this case:
Select ([Field1] & [Field2]) AS FieldAlias

No, as can easily be seen by adding Bob's test case to aTests. The RegExp's
pattern uses "list of words separated by ." to find the real fieldname(s).
If you want to match arbitrary expressions before the AS, you can't use
RegExps, because they aren't meant for bracketed (hierarchical) structures.
Maybe you can use "SELECT", ",", and "AS" to cut the expression(s) from the
SQL statement, but then you risk nasty cases like "(LName & ', ' & FName)".

So my q&d example works for easy cases; some additional work could make it
suitable for a restricted format - you could use "(( " and " ))" to bracket
the names/expressions you are interested in - but there comes the point
where it would be more sensible to collect/store the real names/expression
before you build/execute the statement. Another - definitely not quick -
strategy: write a parser for (the relevant subset of) SQL.
.

Relevant Pages

  • Re: How can i get the "true" name of an aliased field?
    ... Dim aTests: aTests = Array(_ ... Dim oRE: Set oRE = New Regexp ... Dim sTest, oMTS, oMT ...
    (microsoft.public.data.ado)
  • Re: Find newest file in a series of folders
    ... I left the Greman date format because I don't know what to put in it's ... WScript.Echo oMTS.Count just before the IF and the oMTS count is always 0. ... Dim sTeam: sTeam = oFS.GetFileName ... Dim sTxt: sTxt = oExec.Stdout.ReadAll ...
    (microsoft.public.scripting.vbscript)
  • Re: better way to enumerate subfolders by name
    ... Dim aTests(15) ... For nIdx = 0 To UBound(aTests) ... sPrfx = Left ...
    (microsoft.public.scripting.vbscript)
  • Re: Find newest file in a series of folders
    ... Dim sTeam: sTeam = oFS.GetFileName ... WScript.Echo "Starting in", sRoot, sTeam ... Dim sTxt: sTxt = oExec.Stdout.ReadAll ... Dim oMTS: Set oMTS = reFile.Execute ...
    (microsoft.public.scripting.vbscript)
  • Re: Find newest file in a series of folders
    ... Dim sTeam: sTeam = oFS.GetFileName ... WScript.Echo "Starting in", sRoot, sTeam ... Dim sTxt: sTxt = oExec.Stdout.ReadAll ... Dim oMTS: Set oMTS = reFile.Execute ...
    (microsoft.public.scripting.vbscript)