SQL WHERE Like Clause not returning all results

Question

I have a strange one that I have been scratching my head over for the past few hours.

I have a relatively simple TSQL

SELECT 
  LTRIM(RTRIM(bom_comp_code)) [bom_comp_code]
  , bom_product                 
  , bom.actual_partnumber           
FROM 
  dbo.MRP_ALL bom 
WHERE               
  bom.bom_product = 'F00434'

This returns 500 rows of a result like - perfect

[bom_comp_code]  [bom_product]  [actual_partnumber]
M03275           F00434         99292922            
M03275           F00434         99292922            
B01869           F00434         99292922            
B01869           F00434         99292922            
M03275           F00434         99292922            
M03275           F00434         99292922            
B01869           F00434         99292922            
...      
...
B01869           F00434         99292922            
B01869           F00434         99292922            
M03275           F00434         1110-011        
M03275           F00434         1110-011

Now I am only interested in bom_comp_code that start with an M, so naturally I use the following TSQL

SELECT 
  LTRIM(RTRIM(bom_comp_code)) [bom_comp_code]
  , bom_product                 
  , bom.actual_partnumber           
FROM 
  dbo.MRP_ALL bom 
WHERE               
  bom.bom_product = 'F00434' 
AND LTRIM(RTRIM(bom.bom_comp_code)) like 'M%'

But this only returns one row!

[bom_comp_code]  [bom_product]  [actual_partnumber]
M03275           F00434         1110-011

I cannot for the life of me understand why only one row is returned when there are 224 in the table that match my criteria

The LTRIM / RTRIM was me thinking there was whitespace around the bom_comp_code field, but this did not work.

I have also tried this around bom_product too just in case.

What could possible be stopping all my rows from returning?

`LIKE 'M%'` should totally work. What happens when you do a `LIKE '%M%`'? Just out of curiosity. — JNevill, Nov 16 '17 at 16:01
cast a non-matching M% row to varbinary and look at the lead value to ensure the first byte is 0x4d as opposed to a unicode artifact (from say a copy/paste) — Alex K., Nov 16 '17 at 16:10
As noted elsewhere, the trim functions only remove spaces. [Ref](https://stackoverflow.com/a/35245374/92546). You can trim additional whitespace characters with code like [this](https://stackoverflow.com/questions/35245812/whats-a-good-way-to-trim-all-whitespace-characters-from-a-string-in-t-sql-witho/35247507#35247507). — HABO, Nov 16 '17 at 16:12
If your column is a NVARCHA type use instead `N'M%'`. It can be a collation or unicode problem like I once got with numbers and the square and cube symbols — jean, Nov 16 '17 at 16:28
@JuanCarlosOropeza Sorry not being clear. All my 224 Mxxx rows. It just so happens M is only used as the first character in the database table — Ian, Nov 16 '17 at 16:28
@jean interesting. I will check the table structure when I get back to work. I'm not sure how it's set up as it's not my database. — Ian, Nov 16 '17 at 17:04
@jean Performing LIKE N'M%' worked. I am guessing it's a collation issue. Thanks for the help. — Ian, Nov 17 '17 at 08:07

Juan Carlos Oropeza · Answer 1 · 2017-11-16T16:10:44.960

1

USE LEFT()

AND LEFT(bom_comp_code, 1) = 'M'

And if you really have spaces

AND LEFT(LTRIM(bom_comp_code), 1) = 'M'

To check if you have special character at begining of your string then debug like this

SELECT LEFT(LTRIM(bom_comp_code), 1) as first_char,
       ASCII (LEFT(LTRIM(bom_comp_code), 1)) as ascii_number
       LTRIM(bom_comp_code)
FROM dbo.MRP_ALL bom

edited Nov 16 '17 at 16:10

answered Nov 16 '17 at 16:02

Juan Carlos Oropeza

47,252
12
78
118

I guess u have given string position – Ven Nov 16 '17 at 16:03
1

Providing an alternative way to do the same thing does not answer why that thing does not seem to be working for the OP - i.e. why `like 'M%'` is not working as they expected. – underscore_d Nov 16 '17 at 16:03
@underscore_d Ok I address that. Probably a special character at beginning – Juan Carlos Oropeza Nov 16 '17 at 16:05
Well LEFT() worked, and returns all mt rows. I included debugging and all bom_comp_code rows start with ASCII 77. I don't get why LIKE M% does not work – Ian Nov 16 '17 at 16:15
1

That doesn't make sense. `LEFT()` should work the same as `LIKE M%` can you dump some data in http://rextester.com so we can try to reproduce the behaviour? Is there any special collations defined? – Juan Carlos Oropeza Nov 16 '17 at 16:16
@JuanCarlosOropeza I struggled to re-produce it. What is really strange is it only (as far as I have found) happens with this one particular combination of bom_comp_code and bom_product. The other several thousand products does not exhibit this behavior which to me suggests something fishy with this one product. I am trying to find out what it is – Ian Nov 16 '17 at 16:24
1

if you cant reproduce then is a problem with your system. Check for collation on the database or the fields. Try create a backup of your db and install in a different server. Export the DDL and try it on rextester. – Juan Carlos Oropeza Nov 16 '17 at 16:27
@JuanCarlosOropeza I agree it's our system, your solution to use LEFT() will get past this issue and will allow me to continue developing. From reading comments, I strongly suspect its a special character issue which I am not suprised as the data would have most likely been entered via copy and pasting – Ian Nov 16 '17 at 16:34

score 0 · Answer 2 · answered Nov 16 '17 at 16:14

I had a similar problem and I had special characters just as "Juan Carlos Oropeza" mentions above.

Using the function in this answer https://stackoverflow.com/a/14211957/7141065, I was able to update the column in question and run queries as normal after that.

SQL WHERE Like Clause not returning all results

2 Answers2