Thursday, February 18, 2016

How to use wildcards with the SQL Server LIKE operator

I answered a question today in one of my groups... a whole lot of LIKE operator and wildcard conditions. Great subject matter! This is just a few different tips for using SQL's LIKE operator with wildcards.  Be sure to look at the data you're loading into the variable, and read my comments for each of the sample statements.  

   /*   table variable */
   DECLARE @DBVersions TABLE (
       DatabaseVersion VARCHAR(25) NOT NULL,
       VersionDate DATETIME NOT NULL,
       ModifiedDate DATETIME NOT NULL )

   /*  load some test data  */
   INSERT @DBVersions (DatabaseVersion,VersionDate,ModifiedDate)
   VALUES ('ssAsxb567','2016-01-14','2015-03-14'),
          ('ss%sxb567','2016-01-14','2015-03-14'),
          ('ABXB23','2016-01-14','2015-03-14'),
          ('aBxb234','2016-01-14','2015-03-14');

   /* just look at the raw data  */
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   /*
     7 different conditions with wildcard
   #1 return anything that contains OR begins with the letter 'A'
   #2 return anything beginning with 'A', but does not contain it elsewhere
   #3 return anything that contains the letter 'A', but does not begin with it
   #4 return anything LIKE the given DatabaseVersion, but is in ALL CAPS
   #5 return anything that LIKE the given DatabaseVersion, regardless of CASE
   #6 return anything that has 3 digits the given DatabaseVersion
   #7/8 return anything that has the % literally within the DatabaseVersion      */

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'
   AND DatabaseVersion NOT LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' COLLATE Latin1_General_CS_AS; --only UPPERCASE

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' -- you'll see we get them both back on this one

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX___' -- now only one
 
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%' -- why do we get them all?!

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%[%]%' -- use the brackets as an escape character
                                        -- first and last %s are the WILDCARD
                                        -- the middle one is treated as a literal
                                        -- good stuff  

That's all I've got for now, but I may add more later. Take a look at this for more information, and more examples:

No comments:

Post a Comment