How to search between two "Like" fields in SQL Server 2012

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:

Statement Description
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
Advertisements