Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
On the left side, we have a veteran warrior with 37 years of data battle scars and redundant six pack. They call him SQL.
On the right side, there is a young challenger with transformative powers and “never say undo” attitude. He goes by the moniker Power Query.
Who is going to win this battle?!?
I have been using SQL for 25 years and Power Query since it came out in early 2012. And in this article, let me share my views on how SQL compares with Power Query. If you prefer to listen, check out the podcast episode – SQL vs. Power Query.
Listen: SQL vs. Power Query Podcast Episode
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
SQL vs. Power Query - The Comparison
SQL
Power Query
What can you do?
All CRUD operations (Create, Read, Update, Delete)
Only Read the data
What kind of data?
Usually single source from a database or warehouse
(ex: SQL Server)
Can access data from anywhere and combine data from multiple sources too.
How do you use it?
You need to “WRITE” queries to use SQL.
You “BUILD” Power Queries using the UI buttons and menu options.
Where can you use it?
Works almost universally. You can use SQL with most database systems and programming languages.
Only with Microsoft stack of products, primarily with Power BI, Excel and Fabric.
Who can use it?
By default, you need permissions / special software to use SQL.
Almost anyone can use Power Query as it comes packaged with Excel and Power BI.
How fast is it?
Built for performance and scalability. You can use SQL to access data quite efficiently.
Can become slow and tedious as your data grows.
Resources for Learning SQL
Resources for Learning Power Query
What do you think?
Have you used both or either of these technologies? What do you think? Leave a comment with your thoughts.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub