Split SQL Statements on Keywords

Hi,

At work I have been asked to tell my manager what is different between 2 text files. The data in the text files is a bunch of SQL Statements. Most of them are super long strings. I have the comparison part down & know what is different. I have to now build a report to outline the differences. They said they would accept it in Word or Excel. I am opting for xl.

The part I need help with is some VBA logic to break the SQL Statement into smaller bite size chunks. I was thinking of trying to use VBA's Split function to split the SQL Statements on SQL Keywords.

Maybe there is a program that already does this. Like a feature in Sublime Text or something. If there is please mention it.

If you know of a post where someone has already worked something like this out please point me in the right direction. I can’t be the 1st or only person who has had to do this.

TIA

I don’t know if SPLIT is the right approach. I see now that SPLIT wants to replace the delimiter I am using with a LineBreak. If that’s the case I might as well use REPLACE. Right? Seems to make sense. Let me see what I can cook-up with that.

I tried posting my code but I am getting a SQL injection message. Weird I'm not doing anything malicious.

Can't you just use a text comparison tool and output the differences? WinDiff, git or similar?

Does Word's Compare not suffice? If it does, we could probably help automate it for you. Manually doing it seems simple enough though.

https://support.office.com/en-us/art...ad=US&fromAR=1

Yeah, I could if I knew about them. Thanks for telling me @Kyle. I will test them & see if they provide the solution.

lol I was just thinking about it & to test it I need to put in a request with IT. Wait for it to be approved & then wait for them to install it for me. That could take 3-72 hours. lol... yeah

I will put in the request, but I can't bank on IT getting anything installed on my machine before tomorrow morning when I have my meeting.

http://www.quickdiff.com/
and to make the SQL consistent http://www.dpriver.com/pp/sqlformat.htm

Hi Ken,

I am not having a problem with comparing the files. Truly my problem is following the SQL Queries. They are so..... long [Some of these statements go from Col A - DD]. I want to BREAK them ON SQL Keywords. It would look something like below if the Forum Server lets me post it. [Fingers crossed]

Nope it wont let me post it. [SAD face]

Haha I bet they're not half as long as some of the SQL I've written, I've produced some monsters in my time that I hope I never have to go back to change!

Try the links I posted above, the sql formatter will make them look consistent and make for easier comparison, then the diff tool will tell you what's different

Here is a before & after of the results I am trying to achieve.
Before
B4.PNG
After
After.PNG

The second link I posted does that, though you may have to uncomment

lol @Kyle. Yeah you know what I am talking about. Thanks again for the insights I am going to get on making those requests. While I wait I will test on my own Rig with some sample code. Worst case I will just have to tell them I am waiting on IT. lol Hurry-up & wait.

I think I am starting to get my head wrapped around what I was needing to do.
Count the total words (For a ceiling for my loop)
Find my Keywords (Counter for my CaseStatement in a For loop)
Finally Loop through my CaseStatement & make the modification’s via REPLACE to the SQL Query & Flag each Keyword as it is done, so hopefully I don’t have any problems.

I think what you're trying to do makes no sense, there are tools that are on the web that don't need downloading that do what you want.

But hey, if you like making work for yourself

GREAT!!! @Kyle THANKS A TON.

I guess I dont have to do all that mess after all. lol....

Yup Im testing that link now. IT WORKS!!!! Thanks @Kyle

Glad it helped