CODEWITHSUNDEEP
t-sqlwildcardspacesql-like
Steve
Steve

Reputation: 2193

tsql using like with wildcard and trailing space?

I cannot get the like statement to work with space and trailing wildcard. My query goes as follows:

select * from Table where Field like 'Desc_%'

The data is space-delimited such as, Desc Top, Desc Bottom and so on. The query works when I use the pattern 'Desc_%' but not when I use the pattern 'Desc %'. The field is nvarchar(255).

Any ideas?

EDIT

Turns out the data was tab-delimited and when I copied a value from the 2008 Management Studio it converted the tab to space. Dumb mistake. I did like the [ ] tip so I marked it the answer. Thanks everyone, I'll remember not to trust the copy from the grid results.

Upvotes: 6

Views: 8690

Answers (3)

p.campbell
p.campbell

Reputation: 100577

Consider explicitly stating that you want a space, using its ASCII value?

SELECT * FROM Table WHERE Field Like 'Desc' + CHAR(32) + '%'

Upvotes: 0

DaveE
DaveE

Reputation: 3637

Use brackets '[' & ']' to set up a single-character class to match. In your case the SQL should look like this: "select * from Table where Field like 'Desc[ ]%'"

EDIT: add sample, link

CREATE TABLE #findtest (mytext  varchar(200) )

insert #findtest VALUES ('Desc r')
insert #findtest VALUES ('Descr')

select * from #findtest where mytext like 'Desc[ ]%'

DROP TABLE #findtest

(1 row(s) affected)

(1 row(s) affected)
mytext
--------
Desc r

(1 row(s) affected)

See this article.

Upvotes: 4

MJB
MJB

Reputation: 7686

Since an underscore is a single character wildcard, and percent is the multi-char wildcard, they are the same ( "%" and "_%" ). It is as if you are asking for two consecutive wildcards. Not sure if I understand your question, but I am not surprised it does not behave the way you expect.

Upvotes: 0

Related Questions