CODEWITHSUNDEEP
sqlsql-server
mary
mary

Reputation: 267

SQL LIKE Operator just work when using %

When I write this query in SQL :

select * from words where  v like 'win'

SQL returns null. But when I try this one:

select * from words where  v like '%win'

or this one 

select * from words where  v like '%win%'

It works and returns all words including win. I have 'win' in my cells why for first query It returns null value!? I used rtrim(v) for omitting space but It did not work.

Upvotes: 0

Views: 91

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

Most likely there are white characters in your text.
Execute the following code on the rows with 'win' value in order to verify.

select cast(v as varbinary(max)) from words where ...

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

This will show any records with control characters

Select *
 From  Words
 Where CharIndex(CHAR(0),[v])+CharIndex(CHAR(1),[v])+CharIndex(CHAR(2),[v])+CharIndex(CHAR(3),[v])
      +CharIndex(CHAR(4),[v])+CharIndex(CHAR(5),[v])+CharIndex(CHAR(6),[v])+CharIndex(CHAR(7),[v])
      +CharIndex(CHAR(8),[v])+CharIndex(CHAR(9),[v])+CharIndex(CHAR(10),[v])+CharIndex(CHAR(11),[v])
      +CharIndex(CHAR(12),[v])+CharIndex(CHAR(13),[v])+CharIndex(CHAR(14),[v])+CharIndex(CHAR(15),[v])
      +CharIndex(CHAR(16),[v])+CharIndex(CHAR(17),[v])+CharIndex(CHAR(18),[v])+CharIndex(CHAR(19),[v])
      +CharIndex(CHAR(20),[v])+CharIndex(CHAR(21),[v])+CharIndex(CHAR(22),[v])+CharIndex(CHAR(23),[v])
      +CharIndex(CHAR(24),[v])+CharIndex(CHAR(25),[v])+CharIndex(CHAR(26),[v])+CharIndex(CHAR(27),[v])
      +CharIndex(CHAR(28),[v])+CharIndex(CHAR(29),[v])+CharIndex(CHAR(30),[v])+CharIndex(CHAR(31),[v])
      +CharIndex(CHAR(127),[v]) >0

Since you only have /n and /t

Update Words set V = replace(replace(replace(v,char(10),''),char(13),''),char(9),'')

Upvotes: 2

pacreely
pacreely

Reputation: 1931

If you're still getting empty cells then this might be an issue with SSMS "Results to Grid", change to "results to text" (CTRL-T) it might throw more light on the true nature of your source data.

Upvotes: 0

Related Questions