1

I'm not sure how to put it into words. I have this select in a ms sql table

select * from nomencl where Denumire='NGT2-65/201-32/1.3-72(DO) BMT 65 ,radial tool holder, DOOSAN'

and it returns nothing.

But if I use : 

select * from nomencl where Denumire LIKE 'NGT2-65/201-32/1.3-72(DO) BMT 65 ,radial tool holder, DOOSAN%'

the record is there.

I'm not very skilled with ms sql, but I need to make it work. What could be the problem? Is it something in that string? I've searched for spaces at the end of the string but still found nothing.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
byte00
  • 43
  • 6
  • 2
    Sounds like you have some non-printable characters after you visible string (could be, for example, an enter (Carriage Return/Line Feed)) – HoneyBadger Sep 03 '19 at 13:53
  • 1
    Those statements aren't equivalent. One has an equals (`=`) operator; which means the 2 values must be the **same**, where as the other uses `LIKE` and a trailing wildcard; meaning that the 2 values simply need to start with the same characters. – Thom A Sep 03 '19 at 13:54
  • 2
    Possible duplicate of [Equals(=) vs. LIKE](https://stackoverflow.com/questions/543580/equals-vs-like) – Amira Bedhiafi Sep 03 '19 at 13:55

2 Answers2

5

It's because there is no row where Denumire equals 'NGT2-65/201-32/1.3-72(DO) BMT 65 ,radial tool holder, DOOSAN'. However, there is a row where Denumire starts with that value. The % on the end with LIKE denotes this.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • The record exists, and that is the value: 'NGT2-65/201-32/1.3-72(DO) BMT 65 ,radial tool holder, DOOSAN'. It's intriguing why it shows only when I use LIKE. – byte00 Sep 03 '19 at 13:55
  • No, it doesn't @byte00. There is something else at the end... possible a CR/LF. But that literal doesn't exists. It would work like so [DEMO](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=de061aac28d1e437f6f7543f72977a69). Note, spaces at the end of the string would *not* matter for the `=` operand. – S3S Sep 03 '19 at 13:58
  • I agree with @scsimon as I have also tested this in sql server. It is working properly for = operator as well as LIKE – Chathuranga Ranasinghe Sep 03 '19 at 14:06
  • @scsimon How can I find if that is the case? – byte00 Sep 03 '19 at 14:07
  • 1
    @byte00 You could start with `SELECT ASCII(RIGHT(Denumire,1)), UNICODE(RIGHT(Denumire,1)) FROM dbo.nomencl where Denumire LIKE 'NGT2-65/...';` - I bet the answer is not `78`. – Aaron Bertrand Sep 03 '19 at 14:09
0

LIKE is the ANSI/ISO standard operator for comparing a column value to another column value, or to a quoted string. It returns either 1 (TRUE) or 0 (FALSE).

The equals to(=) operator is a comparison operator and used for equality test within two numbers or expressions.

LIKE is generally used only with strings however equals (=) is used for exact matching and it seems faster.

mohamed-mhiri
  • 202
  • 3
  • 22
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • It's strange because that is the value, it should have shown it when I use equal. However it shows when I use LIKE. – byte00 Sep 03 '19 at 14:02
  • @byte00 use `LIKE` and remove the `%` and your row will **not** be returned as well. Since you are using `%` it is equating to a string that starts with that value. – S3S Sep 03 '19 at 14:04