Sometimes you will be asked to filter information and what better way of doing this than using “Like” filters in SQL Server.
Flexible and easy to use, the two types of filter (The percent sign (%) and The underscore (_)) can be used as placeholders for one or more characters.
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
Here are number of examples showing WHERE part having different LIKE clause with ‘%’ and ‘_’ operators:
|WHERE SALARY LIKE ‘200%’||Finds any values that start with 200|
|WHERE SALARY LIKE ‘%200%’||Finds any values that have 200 in any position|
|WHERE SALARY LIKE ‘_00%’||Finds any values that have 00 in the second and third positions|
|WHERE SALARY LIKE ‘2_%_%’||Finds any values that start with 2 and are at least 3 characters in length|
|WHERE SALARY LIKE ‘%2’||Finds any values that end with 2|
|WHERE SALARY LIKE ‘_2%3’||Finds any values that have a 2 in the second position and end with a 3|
|WHERE SALARY LIKE ‘2___3’||Finds any values in a five-digit number that start with 2 and end with 3|
When faced with the problem of using “Likes” at the end of a range of searches, we decided to go with a simple solution that determines the start number of a like sequence for both ends of the scope and then does a “Between” search.
An additional code will check whether the end range exists and is smaller than the start range. If not, the two values will be switched.
Full Code below:
DECLARE @SQL nvarchar(1600), @SqlModified nvarchar(1500) IF @Exclude=1 SET @SQL = 'DELETE FROM tblUserVendor WHERE UserID=' + cast(@UserID as varchar(20)) +' AND SupplierNumber in (Select Number from tblSuppliers WHERE ' ELSE SET @SQL = 'INSERT INTO tblUserVendor (UserID, SupplierNumber) SELECT ' + cast(@UserID as varchar(20)) + ', Number FROM tblSuppliers WHERE ' IF @StartRange<>'' BEGIN SET @SqlModified = @SQL + ' Number like ''' + @StartRange + '''' END --get character range if end range is specified if @EndRange<>'' BEGIN declare @endChar nvarchar(1), @StartChar nvarchar(1), @i int SET @endChar = LEFT(@EndRange,1) SET @StartChar = LEFT(@StartRange,1) IF @StartChar = @endChar BEGIn --get first number matching SET @SqlModified = ' DECLARE @st nvarchar(200), @end nvarchar(200) SET @St = (SELECT TOP 1 Number from tblSuppliers where Number like ''' + @StartRange + ''') SET @end = (SELECT TOP 1 Number from tblSuppliers where Number like ''' + @EndRange + ''') SET @ST = ISNULL(@st,'''') SET @end = ISNULL(@end,'''') DECLARE @switch nvarchar(200) IF @St>@end BEGIN SET @Switch = @St SET @St = @End SET @end = @Switch END ' + @SQL + ' Number between @st and @end ' END ELSE BEGIN --if we have a range like 0% to A%, we take the first ascii char and we do likes on the followings (1,2,3,... 9). We then add the A like. SET @i = ASCII(@StartChar) + 1 WHILE @i<ASCII(@endChar) BEGIN SET @SqlModified = @SqlModified + ' OR Number Like ''' + CHAR(@i) + '%'' ' SET @i = @i + 1 END END --add end range like SET @SqlModified = @SqlModified + ' OR Number Like ''' + @EndRange + ''' ' END IF @Exclude=1 SET @SqlModified = @SqlModified + ')' IF @NamesOnly=1 SET @SqlModified = REPLACE(@SqlModified, ' Number',' Name') --print @SQLModified exec sp_executeSQL @SQLModified