SQL Server query to match the exact word

Question

This query returns the following:

select * 
from dbo.persons 
where LastName like '%Dan%';

Results:

How can I select only the first row with LastName = "Dan" and not "Dant" or "Dan12" ?

I am using the following query:

select * 
from dbo.persons 
where LastName like '%[^a-z]Dan[^a-z]%' 
   or LastName like 'Dan[^a-z]%' 
   or LastName like '%[^a-z]Dan'

which returns the following:

LastName = Dan12
LastName = pine and Dan and apple

I want only these results:

LastName = Dan
LastName = pine and Dan and apple

I tried the following query but it returned nothing:

select * 
from dbo.persons 
where LastName like '%[^a-z][^0-9]Dan[^a-z][^0-9]%' 
   or LastName like 'Dan[^a-z][^0-9]%' 
   or LastName like '%[^a-z][^0-9]Dan'

Use equals instead of `like` -- `where lastname = 'dan'` ... — sgeddes, Jun 20 '18 at 13:44
Possible duplicate of [What's the difference between "LIKE" and "=" in SQL?](https://stackoverflow.com/questions/1504990/whats-the-difference-between-like-and-in-sql) — S3S, Jun 20 '18 at 13:48
Why is downvoted? Besides from he can google it. Its a questions which is pretty clearly. — SqlKindaGuy, Jun 20 '18 at 13:48
@plaidDK - that's reason enough to downvote, as it shows a lack of research. — user2366842, Jun 20 '18 at 13:49
@user2366842 Youre right. I just hoovered over the wrong place. I can see now that downvote is when its for lacking of research efforts. Always thought it was due too lack of information :) — SqlKindaGuy, Jun 20 '18 at 13:50
@plaidDK beside research the logic isnt clear. is just the Dan you are looking for or just the first one alphabetical — Juan Carlos Oropeza, Jun 20 '18 at 14:03

score 2 · Answer 1 · answered Jun 20 '18 at 13:46

2

Simply

select * from dbo.persons where LastName ='Dan';

answered Jun 20 '18 at 13:46

Ilyes

14,640
4
29
55

1

This solution does not work. It matches 'Dan ' (extra space at the end) also. – The Impaler Jun 20 '18 at 14:24
@TheImpaler You are very wrong here. `=` wont match any extra space. And the weird part is you also have the exact same query but you said Sami is the one wrong? – Juan Carlos Oropeza Jun 20 '18 at 15:04
2

I can't believe what you are saying. Am I going crazy here? :D I have SQL Server 2014. I just tried and equals (=) return the 'Dan ' (extra space at the end). For the record, I tried to down vote my answer, but doesn't allow me to. What version of SQL Server are you using? Is your column `char` or `varchar`? Mine is `varchar`. – The Impaler Jun 20 '18 at 19:25
@TheImpaler extra spaces are ignored in equality operators in SQL Server so you aren't crazy. – S3S Jun 22 '18 at 19:12

score 2 · Answer 2 · answered Jun 20 '18 at 13:47

2

Both of the statement below will do what you want:

select * from dbo.persons where LastName like 'Dan';

or:

select * from dbo.persons where LastName = 'Dan';

answered Jun 20 '18 at 13:47

The Impaler

45,731
9
39
76

I'd arguably state the second of these two is better practice, as it's a bit more direct on exactly what you're looking to pull back. – user2366842 Jun 20 '18 at 13:48
for the sake of the argument, the second will return a row with value 'Dan ', while the first will not :) – George Menoutis Jun 20 '18 at 13:58
@GeorgeMenoutis I just tried both of them against 'Dan ' (extra space at the end), and to my surprise BOTH find it. I can't believe even equals (=) also matches the `varchar` column. Is this a SQL Server bug? – The Impaler Jun 20 '18 at 14:23
This is strange, are you sure? To my knowledge, = disregards trailing spaces, but LIKE doesn't. – George Menoutis Jun 20 '18 at 14:30
Just tried both (like and equals). I tried with extra spaces at the beginning, at the end, and both places. The bug is for spaces at the end only. Please downvote my answer, and upvote the question. – The Impaler Jun 20 '18 at 14:31

Ilak · Answer 3 · 2018-06-20T15:49:01.710

1

I used the following query that seems to work:

  select * from dbo.persons where LastName like '%[^a-z]Dan[^a-z]%' OR 
                                  LastName like 'Dan[^a-z0-9]%' OR 
                                  LastName like '%[^a-z]Dan' OR
                                  LastName like 'Dan';

These are rows that are returned which are expected.

edited Jun 20 '18 at 15:49

answered Jun 20 '18 at 15:40

Ilak

158
3
13

Thanks to the following post:https://stackoverflow.com/questions/5444300/search-for-whole-word-match-with-sql-server-like-pattern This post helped me in framing this query. – Ilak Jun 20 '18 at 15:41
You can mark this answer as correct so everyone know this is already solved. – Juan Carlos Oropeza Jun 21 '18 at 15:11
@JuanCarlosOropeza I have to wait for another day to accept this answer. – Ilak Jun 21 '18 at 16:37

Juan Carlos Oropeza · Answer 4 · 2018-06-20T15:05:51.590

0

Im going to guess you dont know which one is the first one so this doesn't work:

where LastName = 'Dan'

instead you select the first one order alphabetical

select TOP 1 * 
from dbo.persons 
where LastName like '%Dan%'
ORDER BY LastName ;

edited Jun 20 '18 at 15:05

answered Jun 20 '18 at 14:10

Juan Carlos Oropeza

47,252
12
78
118

This solution does not work. It also matches 'Dan ' (extra space at the end). – The Impaler Jun 20 '18 at 14:29
@TheImpaler This solution works as I explain on the answer. You assume OP want `Dan` , I assume OP want the first row matching the where clausule. – Juan Carlos Oropeza Jun 20 '18 at 14:59
@JuanCarlosOropeza The OP says 'Dan', which makes your A correct too. – Ilyes Jun 20 '18 at 16:07
@Sami OP want the first result matching the pattern. In this case is `Dan` but you dont know that before perform the query. – Juan Carlos Oropeza Jun 21 '18 at 15:09

aiai · Answer 5 · 2018-06-20T15:32:40.020

0

Instead of using LIKE which check any occurrence of a character or a set of characters by using the wildcard % use = to get the exact match you need. If there will be a trailing whitespace at the beginning or at the end it will not get the exact match so use LTRIM() or RTRIM() to get rid of the whitespaces.

SELECT * FROM dbo.persons WHERE LastName = LTRIM(' Dan')

OR

 SELECT * FROM dbo.persons WHERE LastName = RTRIM('Dan ')

edited Jun 20 '18 at 15:32

answered Jun 20 '18 at 14:12

aiai

1,129
2
11
21

This solution does not work. It matches 'Dan ' (extra space at the end) also. – The Impaler Jun 20 '18 at 14:26
@TheImpaler i think it will not only work if there will be a space in the beginning , not at the end, if this will be the case then trim for the space at the beginning so it will still get the exact match – aiai Jun 20 '18 at 14:46

SQL Server query to match the exact word

5 Answers5

Linked