Search code examples
sqlsql-serverstringsql-like

SQL Server - LIKE Operator Works When Searching Partial String But Not Whole String

I am experiencing an issue with my SQL Server database that, truthfully, I'm unsure how to even describe. Therefore, I will try to demonstrate it, as best I can.

I have a table containing several rows where the value in the [Meter Name] column is 

Electric Meter #140002054 Acct.2013147 House, south mall service &&parking lot

Now, when I run the following query, I get exactly the result I would expect.

select distinct [Meter Name] 
from Last_PM_Export
where [Meter Name] like 'Electric Meter #140002054 Acct.2013147 House, south mall service%'

Note that I am using a % wildcard, cutting off the search pattern immediately after the word "service". I get the following result.

Expected result using partial string

Again, this is exactly what I would expect. No problem.

Now, observe what happens when I extend the pattern to include the space that comes immediately after the word "service".

select distinct [Meter Name] 
from Last_PM_Export
where [Meter Name] like 'Electric Meter #140002054 Acct.2013147 House, south mall service %'

Unexpected result using partial string

As you can see, the query returns no results. I have also tried querying for the entire string, as is, copied directly out of the table.

select distinct [Meter Name] 
from Last_PM_Export
where [Meter Name] like 'Electric Meter #140002054 Acct.2013147 House, south mall service &&parking lot'

The result is the same as the query immediately above. No results.

I cannot think of any reason this should be happening. It flies in the face of how I understand the LIKE operator to work. I've consulted with our in-office IT person, and he agrees that this is unusual.

Again, I know this isn't a very good description of the problem, but hopefully the demonstration will suffice. Also, my apologies for not being able to share the table or the database more fully, but I cannot do so for reasons of confidentiality.

Any assistance would be greatly appreciated. Thank you.

Solution

  • Not an answer, but to much for a comment... Would you please confirm, that this an exact scenario of your issue? As you can easily check out, both SELECTs come back with the right rows...

    DECLARE @mockup TABLE([Meter Name] VARCHAR(1000));
INSERT INTO @mockup VALUES('Electric Meter #140002054 Acct.2013147 House, south mall service &&parking lot')
                         ,('Electric Meter #140002054 Acct.2013147 House, south mall service &&some other')
                         ,('different');

    --Without a blank

    SELECT * FROM @mockup 
where [Meter Name] like 'Electric Meter #140002054 Acct.2013147 House, south mall service%';

    --With blank

    SELECT * FROM @mockup 
where [Meter Name] like 'Electric Meter #140002054 Acct.2013147 House, south mall service %';

    Might be there is something more, you reduced for brevity? Are you able to change the above mock-up in order to make your issue reproduceable?