1

This one matches column_name like 'CharEndsHere%'

and

This one doesn't column_name like 'CharEndsHere'

I know that like operator will consider even the trailing spaces, so I just copied the exact column value (with trailing spaces) and pasted it.

Something like column_name like 'CharEndsHere ' yet it doesn't match -- why?.

I haven't used '=' operator since the columns type is ntext

Is there something I am missing here or shouldn't I use like operator in this way?

Edited : column_name like 'CharEndsHere__' (__ denoted the spaces) 'CharEndsHere ' is the exact value in that cell, using like in this way valid or no?

Edit :

This is the code I tried,

SELECT * 
FROM [DBName].[dbo].[TableName] 
WHERE [DBName].[dbo].[TableName].Address1 LIKE rtrim('4379 Susquehanna Trail S  ') 

I have also tried without using rtrim, yet the same result

Edit: According to Blindy's answer,

If a comparison in a query is to return all rows with the string LIKE 'abc' (abc
without a space), all rows that start with abc and have zero or more trailing 
blanks are returned.

But in my case, I have queried, Like 'abc' and there is a cell containing 'abc '(with trailing spaces) which is not returned. That's my actual problem

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vignesh Paramasivam
  • 2,360
  • 5
  • 26
  • 57
  • LIKE without any wildcards (% or _) will work exactly like the = operator! – Mithrandir Aug 06 '14 at 13:51
  • @Mithrandir Yes I mentioned in the question too. But what im looking for here is like 'char__' to match without using % – Vignesh Paramasivam Aug 06 '14 at 13:55
  • Then you should state that in your question! You want to use the like operator in a way it can't be used. Keep wanting it, it won't change the way like works! – Mithrandir Aug 06 '14 at 13:57
  • @Mithrandir Yes as u said, but i just want to know will it work or no. i had edited my question. – Vignesh Paramasivam Aug 06 '14 at 14:00
  • 2
    `LIKE` is fine - but you should **stop using** the `NTEXT` datatype! Use `NVARCHAR(MAX)` instead. `NTEXT` was crappy and many string functions don't work at all on it. Use `NVARCHAR(MAX)` for all your extra-long Unicode strings and you'll be fine! – marc_s Aug 06 '14 at 14:21
  • 1
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Aug 06 '14 at 14:22

1 Answers1

7

This is a case of reading the documentation, it's very explicitly stated here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

Edit: According to your comments, you seem to be looking for a way to use like while ignoring trailing spaces. Use something like this: field like rtrim('abc '). It will still use indexes because rtrim() is a scalar operand and it's evaluated before the lookup phase.

Blindy
  • 65,249
  • 10
  • 91
  • 131
  • 1
    This makes sense. Out of curiosity, what does this mean `The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs.` which can be found here: [How SQL Server Compares Strings with Trailing Spaces](http://support.microsoft.com/kb/316626) – Daniel B Aug 06 '14 at 13:57
  • 1
    The latter is how `=` works, it pads both sides to the same length, then compares (effectively ignoring trailing spaces on both sides). That's saying `like` doesn't do any padding, so the pattern side trailing spaces are significant (while it still drops the test value side spaces). – Blindy Aug 06 '14 at 13:58
  • So is that mean i can use like 'value__' (__ means the spaces) to get to match the value. – Vignesh Paramasivam Aug 06 '14 at 13:59
  • @Vignesh, that's kind of an ambiguous statement. If you want the spaces to be significant, just use a plain `like`. If you want them *not* to be significant, pass the pattern through `rtrim()` first. – Blindy Aug 06 '14 at 14:01
  • @Blindy the spaces are significant, but even using the plain like doesn't match, thats the real reason i asked this question here. – Vignesh Paramasivam Aug 06 '14 at 14:05
  • @Vignesh, Then post some code that shows the problem. I've tested it in 2005, 2008 and 2012, they both work as explained. Here's a small sample: http://sqlfiddle.com/#!6/d41d8/20677 – Blindy Aug 06 '14 at 14:10
  • @Blindy I have edited my question, and also the rtrim also doesn't work – Vignesh Paramasivam Aug 06 '14 at 14:25
  • Yet as you can see from the fiddle I linked, it works. At this point I'd take a hard look at your code (can't do that for you since you still won't post code I can run and see fail, one select on a table I don't know doesn't help). – Blindy Aug 06 '14 at 14:29
  • http://bugs.mysql.com/bug.php?id=62621 - this link portraits this as a bug, but not sure of it – Vignesh Paramasivam Aug 06 '14 at 14:34
  • This isn't a MySql question. – Blindy Aug 06 '14 at 14:56
  • @Blindy Yes i certainly agrre with you. But i just denoted that i may also occur in sqlserver – Vignesh Paramasivam Aug 08 '14 at 12:38