tsql using like with wildcard and trailing space?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

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