Welcome to the Lounge

Southmountain16-Sep-23 8:49

16-Sep-23 8:49

my business need is to extract all field names and database names associated with these fields:

select a.var1,
       a.var2,
       b.var3,
       b.var4
from db1 a left join db2 b
on a.var5=b.var5
;

my need is to put these vars into this form in Excel workbook:

db1 var1
db1 var2
db2 var3
db2 var4

diligent hands rule....

modified 16-Sep-23 15:03pm.

PIEBALDconsult16-Sep-23 19:25

PIEBALDconsult

16-Sep-23 19:25

It's not something I would want to tackle. I suppose that handling the basics like you show could be doable, but things like subqueries and CTEs would be problematic.

Do you think you'll need to support things like
SELECT X + Y AS Z ... ?

And which flavor of SQL? PL/SQL, T-SQL, etc.

jschell18-Sep-23 5:59

18-Sep-23 5:59

Probably something that I would enjoy doing but not sure I would attempt to say it was "small".

I agree with your examples.

Not to mention is the SQL only going to come from SQL scripts? Or also from other code bases.

RickZeeland16-Sep-23 3:41

RickZeeland

16-Sep-23 3:41

I found these parsers written in C# on GitHub:
sqlparser · GitHub Topics · GitHub[^]

Southmountain16-Sep-23 8:51

16-Sep-23 8:51

thanks for the link. I prefer SQL datatable one.

diligent hands rule....

Nelek16-Sep-23 3:55

Nelek

16-Sep-23 3:55

A good example no, sorry.

But next question I recommend you to write it in the proper section Smile | :)

M.D.V.

If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.

Southmountain16-Sep-23 14:11

16-Sep-23 14:11

I just look for some thoughts here, not a specific language implementation.
I may need to implement my request by other scripting languages such as SAS.

diligent hands rule....

Nelek17-Sep-23 3:38

Nelek

17-Sep-23 3:38

Sorry, for me looks like a question. Not a question about code, but a still a programming question.

M.D.V.

jschell18-Sep-23 6:19

18-Sep-23 6:19

Southmountain wrote:
I may need to implement my request by other scripting languages such as SAS.

Huh?

You want to try to implement a SQL Parser using 'SAS' (whatever that is)?

Seems like a bad idea to me.

I have written interpreters. And one compiler.
I have been using Perl for 40 years and I have parsed SQL using that before. Multiple times.
I have also taken a class on compiler theory so I do know what goes into a parser.

And in my estimation with your general description you are setting yourself up for failure. Or for writing a lot more code than you expect.

Thing that can impact the actual project
1. How structured is the SQL? For example if this is dumped from a SQL database tool the form is exact, but if it is human created files then the form will be all over the place.
2. How much SQL exists? 1,000 lines? 10,000? 1 million? The more lines the more likely variations will exist.
3. How often will this need to happen. Every day? Every day for each customer? Only one time and never again?

Consider the following case
1. You are parsing some SQL script files. By that I mean that there is nothing in those files except SQL.
2. Those script files are in use. So you know absolutely that the SQL is valid.
3. This is one time project
4. The number of files are low (total number of lines are probably less than 10,000)
6. You can add a manual step in the process to deal with the more complex examples.

Given the above, and that I really know how to do the above in Perl, this would be a fairly easy project for me.

I however would NOT attempt to do it in Python. Because I have never used Python.
I would not choose to do it in C# nor Java even though I know those languages very well because I know that Perl is going to be better at doing the real work that is needed.

If the project was much bigger than what I suggested above then I would look at making a real parser. Probably in C# or Java. C++ is an option but since it has been so long since I used that I would prefer one of the others. I would do that even though I has been a long time since I made a real parser (versus just munging a solution.) But that is going to become a problem if I could not find an actual BNF type description of the SQL being targeted. There is no way I would attempt to write my own BNF for any of the major databases without vastly increasing the timeline for the work. (And I have written a BNF before and read others.)

Note also that if there are different types of SQL then one needs a parser for each regardless of how one implements it.

Southmountain18-Sep-23 20:13

18-Sep-23 20:13

Quote:
You are parsing some SQL script files. By that I mean that 1. there is nothing in those files except SQL.
2. Those script files are in use. So you know absolutely that the SQL is valid.
3. This is one time project
4. The number of files are low (total number of lines are probably less than 10,000)
6. You can add a manual step in the process to deal with the more complex examples.

yes, my case meets all of above points. thanks for your info! Rose | [Rose]

most cases, my SQL line count will be less than 500.

I figured out that my path will be: table driven LL(1) syntax directed parsing.
SAS can do table easily. also I figured out how to implement stack and queue today.
So it is very hopeful to me.

diligent hands rule....

jschell19-Sep-23 5:10

19-Sep-23 5:10

Southmountain wrote:
most cases, my SQL line count will be less than 500.

Note that I said total line count. The more sources there are the more chance for variability which will break any hack.

Southmountain wrote:
table driven LL(1) syntax directed parsing.

To do formal parsing you need a formal SQL language definition.

Also been a while but seems that I recall that these days languages might require adjustments (manual) in the parser code to deal with variations that formal language definitions were never designed to describe. But I might not be recalling that correctly. So although the formal spec for SQL itself is fairly simple you are unlikely to be dealing with SQL but rather PL/SQL, TSQL, etc.

Southmountain19-Sep-23 19:42

19-Sep-23 19:42

My case is Teradata SQL.

thanks for the great info! Rose | [Rose]

diligent hands rule....

jschell20-Sep-23 13:08

20-Sep-23 13:08

Southmountain wrote:
Teradata SQL.

I could not find a BNF for that.

In contrast and for comparison it was rather easy to find one for TSQL.

Southmountain20-Sep-23 20:02

20-Sep-23 20:02

I think my need is not a full bloom SQL parser, I just need to get all field names. So I plan to manually craft a parser to fit my situation.

diligent hands rule....

worldle 602 3/6

jmaida15-Sep-23 16:11

jmaida

15-Sep-23 16:11

#Worldle #602 3/6 (100%)
🟩🟩🟩🟨⬜⬅️
🟩🟩🟩🟩⬜⬇️
🟩🟩🟩🟩🟩🎉
https://worldle.teuteuf.fr

"A little time, a little trouble, your better day"
Badfinger

Wordle 819

StarNamer@work15-Sep-23 13:06

StarNamer@work

15-Sep-23 13:06

Wordle 819 4/6

🟩⬛⬛🟨🟨
🟩🟩⬛🟩⬛
🟩🟩🟩🟩⬛
🟩🟩🟩🟩🟩

Amarnath S15-Sep-23 13:53

Amarnath S

15-Sep-23 13:53

Wordle 819 4/6

🟩⬜⬜🟩⬜
🟩⬜⬜🟩⬜
🟩🟨⬜🟩⬜
🟩🟩🟩🟩🟩

GKP199215-Sep-23 19:09

GKP1992

15-Sep-23 19:09

Wordle 819 2/6

⬜🟨🟨⬜🟨
🟩🟩🟩🟩🟩

OriginalGriff15-Sep-23 19:37

OriginalGriff

15-Sep-23 19:37

Wordle 819 2/6*

🟩⬜⬜⬜🟨
🟩🟩🟩🟩🟩

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!

GuyThiebaut15-Sep-23 19:49

GuyThiebaut

15-Sep-23 19:49

Wordle 819 4/6

🟩⬜⬜⬜⬜
🟩🟨⬜🟩⬜
🟩⬜🟨🟩⬜
🟩🟩🟩🟩🟩

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: Wordle 819 - 5 4 me

pkfox15-Sep-23 20:42

pkfox

15-Sep-23 20:42

Wordle 819 5/6

⬜⬜🟨🟨🟨
⬜⬜⬜⬜⬜
🟨🟨🟨🟨🟨
🟩🟩🟩🟨🟨
🟩🟩🟩🟩🟩

Wow I've never had all 5 correct letters and not got the word before

In a closed society where everybody's guilty, the only crime is getting caught. In a world of thieves, the only final sin is stupidity. - Hunter S Thompson - RIP