Excel VBA Introduction Part 58.6 - SQL for Excel Files - Text Criteria and the Like Operator

In this video you'll learn how to write criteria for text fields in SQL queries for Excel. You'll learn about the Like operator and the percentage and underscore wildcard characters. You'll see how to use a CharList to search for a range of characters in a string. You'll also learn how to use a variety of string functions such as Len, Left, Right, Mid, UCase, LCase, InStr and StrComp to create more complex criteria, including making your string comparisons case-sensitive.

Chapters
00:00:00 Topic List
00:00:52 The Basic Setup
00:03:24 Basic Text Criteria
00:05:56 Text Not Equal To
00:06:42 Text Greater Than, Less Than and Between
00:09:36 Text In a List of Values
00:11:26 The Like Operator and Wildcard Character
00:15:25 Multiple Words with Like
00:18:55 Finding a Word Rather than a Sequence of Characters
00:22:05 Tidying Up the Code
00:23:34 The Underscore Wildcard
00:25:38 Combining Wildcard Characters
00:28:25 Using Character Lists or Charlists
00:30:40 Character Sequences
00:31:39 Numbers and Charlists
00:32:18 Multiple Sequences in the Same Charlist
00:33:40 Multiple Character Lists
00:35:05 Text Not In Charlist
00:37:53 Putting Techniques Together
00:42:30 Single Punctuation Characters
00:43:50 Exclamation Marks in Charlists
00:45:55 Hyphens and Charlists
00:47:47 Other Annoying Punctuation Characters
00:50:39 Finding Percentage and Underscore Characters
00:52:51 Finding Square Bracket Characters
00:53:47 Finding Quotation Characters
00:56:55 Using Functions in the Where Clause
00:59:26 Using Other Operators with Function Results
01:02:44 Finding Repeating Characters
01:05:31 Finding Repeating Words
01:06:39 Case Sensitive Comparisons
01:09:09 Case Sensitive Begins With
01:13:08 Case Sensitive Ends With
01:15:10 All Upper Case or Lower Case Strings
01:18:10 Case Sensitive Contains
01:21:08 Finding Characters at a Specific Position in a String
01:23:44 Whats Next

[youssefsedkey]

I wrote this comment while watching, awesome Andy you explain in a very easy way.

[korracos]

Contents like this really helps improving human race productivity. Thank you very much.

[sebastian]

This video series of SQL for excel is not only outstanding but also very well explained. I’ve been programming in VBA excel for many years, have done a great deal of tools for the Company I’ve been working for. However, little did I know about ADO and possibility of using SQL statements in VBA while developing tools over the years. Thanks ever so much for the series and also the way you explain how it works. You’re doing a great job! Thx🙂👍

[aperxmim]

We are learning SQL and a drop of Linux for retrieving data. Many of these techniques I have never used before.

[brrrrrrrrrrr]

this is gold !! almost felt compelled to like this video

[frikduplessis]

Thank you Andrew brilliant tutorial 👌 one can really get creative with WHERE, I REALLY ENJOYED this last two tutorials, 🐶

[kashifkhanspecial]

Thank you so much Andrew, I was waiting for this tutorial, my humble request to you that please make videos that how can we apply joins (Inner, Left & Right Joins) from multiple sheets and select the data, and also through joins how can we update single/multiple columns, append query with joins and conditions.

Once again thank you so much for this tutorial, God bless you.

Thanks
Kashif

[xaviruiz]

As always: Thank you so much for this! I'm learning a lot:)

[tejamarneni]

Thank you Andrew for these awesome tutorials. I have a quick question. Any plans on making videos on Azure for beginners?

[naveendayalan]

Thank you very much for the wonderful session.

I would like request you to kindly make session on to automate the non Microsoft application (ex: Adobe Acrobat, SAP). We are getting monotonous task for converting pdf to ocr format. Kindly help us on this

[VincentHardwick]

When trying to find punctuation characters with special syntactical meaning in SQL using the LIKE operator and a pattern string, I wonder if the Excel ACE provider would support use of an ESCAPE clause? e.g. WHERE [Title] LIKE '%^]%' ESCAPE '^' to find anything with a closing square bracket in it

From watching this series, it seems to offer quite full support for other operators, functions and syntax, so I think it might work.