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