CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clausesql-like
Ian
Ian

Reputation: 137

SQL WHERE Like Clause not returning all results

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?

Upvotes: 3

Views: 2232

Answers (2)

Chana T
Chana T

Reputation: 38

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.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

Upvotes: 1

Related Questions