Working in a solutions consulting organization has it’s own challenges and benefits. This includes the opportunity to discuss the features/challenges and solutions that are being implemented across the organization.

Recently, one of my colleagues (let’s call him Jim) called me up to discuss the implementation for a particular requirement in their project. The requirement was to implement a windows like wild-card search in SQL Server.

Jim: Good morning, Nakul! Do you have some time to discuss possible implementation for a requirement that I am working on?

Me: Sure. Please go ahead, I will try to do my best to help you out.

Jim: The product that I am working on has a document management feature that allows users to upload files into the system. The users can then search for these files and then take a bunch of actions on the results. The problem is that on the application side, the user experience has to be same as that offered by the Windows operating system, i.e. the user will search using an asterisk [*] as the wild-card character. SQL Server does supports a percentage/modulo [%] as the wild-card, so the two requirements are conflicting.

Me: That’s an interesting feature. One of the first things that come into mind is that you can do a replace on * with %. Did you try it out?

Jim: I could probably wrap the search into a stored procedure and do that. Let me try it out.

Me: Sure. Take your time. Let me know how it goes.

[After 10-15 minutes…]

Jim: We do seem to be making progress, however, the percentage character [%] is a valid character in a filename. SQL Server treats it as a wild-card and the query does not work as expected.

Me: Hmm…ok. Let me thing over it for a while. I will get back to you in the next half-hour.

[After 30 minutes…]

Me: Hello, Jim! It looks like I have a solution for you. I searched the Books On Line and based on that, I just sent you a query via E-mail. Can you open it? I will walk-you through it.

Jim: Absolutely! I just received your E-mail. Let me open up the query.

Me: In the first part of the query, let us just create a dummy table to hold the test data, and insert some test data into it.

USE tempdb;
GO

--Safety Check
IF OBJECT_ID('FileNames') IS NOT NULL
    DROP TABLE FileNames;
GO

--Create a dummy table to hold the test data
CREATE TABLE FileNames (FileId INT IDENTITY (1,1),
                        FileNameValue VARCHAR(255)
                       );
GO

--Insert some test data
INSERT  INTO FileNames ( FileNameValue )
VALUES  ( 'Nakul Vachhrajani.xls' ),
        ( 'BR.xlsx' ),
        ( 'Blogs.docx' ),
        ( 'Swipe Times for Nakul.xls' ),
        ( 'Trip Details for Nakul.xlsx' ),
        ( 'Sample%File.txt' ),
        ( 'SampleFile.txt' );
GO

Me: As you can see, I have tried to insert test data that covers different file names & types and also has a record with the percentage character [%]. The next part of the query creates a stored procedure that will perform the search.

USE tempdb;
GO

CREATE PROCEDURE dbo.uproc_SearchFileName
    @SearchStringEnteredByUser VARCHAR(255)
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT  dt.FileId,
            dt.FileNameValue
    FROM    FileNames AS dt
    WHERE   dt.FileNameValue LIKE REPLACE(REPLACE(@SearchStringEnteredByUser, '%', '/%'), '*', '%') ESCAPE '/';
END;
GO

Me: As you can see here, the stored procedure basically replaces asterisk [*] with [%]. What it also does is replace the percentage [%] with a combination of a forward-slash and the percentage [/%]. What this allows me to do is to use the ESCAPE keyword to let SQL Server know that it should skip the percentage and not treat it as an escape character. The next part of the script executes this stored procedure in a couple of different ways to show that this logic does work.

USE tempdb;
GO
EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = '*.xlsx';
GO

EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = '*Nakul.xls';
GO

EXEC dbo.uproc_SearchFileName @SearchStringEnteredByUser = 'Sample%*.txt';
GO

Me: So that’s it. A simple REPLACE operation with the use of the ESCAPE keyword can help you achieve this requirement. Did it make sense?

Jim: Absolutely! It’s so simple yet efficient. I will do a bit more testing and tweaking, but I think I am all set now.

Me: I am glad I could help. Have a good day!

Further Reading

Pattern Matching in SQL Server: http://msdn.microsoft.com/en-US/library/ms187489(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.