Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SOLVED: LIKE operator ([ ] and/or %)

Author  Topic 

mallorz
Starting Member

11 Posts

Posted - 2012-09-26 : 15:25:20
This works:

SELECT emp_fname, emp_lname
FROM Employee
ORDER BY emp_lname ASC;

This does not:

SELECT emp_fname, emp_lname
FROM Employee
WHERE emp_lname LIKE '[H]%'
ORDER BY emp_lname ASC;

(nor does..LIKE 'H%')

Normally when something like this happens, its a punctuation thing. However, I went back and compared this to my textbook and it seems right on. What am I missing?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 15:44:00
What did you mean when you said it did not work? I tried the following examples, and they all work as expected/documented.
CREATE TABLE #tmp (emp_lname VARCHAR(32));
INSERT INTO #tmp VALUES ('Hiller'),('Brock'),('[H]older');

SELECT * FROM #tmp WHERE emp_lname LIKE 'H%' -- returns Hiller
SELECT * FROM #tmp WHERE emp_lname LIKE '[H]%' -- returns Hiller
SELECT * FROM #tmp WHERE emp_lname LIKE '[[]%' -- returns [Holder]
SELECT * FROM #tmp WHERE emp_lname LIKE '[H[]%'-- returns Hiller and [H]older

DROP TABLE #tmp;
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 16:18:02
Like sunitabeck wrote, the syntax seems ok, so please post the error you get.
Go to Top of Page

mallorz
Starting Member

11 Posts

Posted - 2012-09-26 : 17:09:32
The first example posted returns all names, in order of last name.

The second one should return all names that have a last name beginning with H. Instead, it returns an empty table. It does not actually give me an error.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 17:23:30
As the syntax is ok, two things comes to mind:

- Are you selecting from the right database?
- Do you receive an timeout?
Go to Top of Page

mallorz
Starting Member

11 Posts

Posted - 2012-09-26 : 18:57:21
quote:
Originally posted by bitsmed

As the syntax is ok, two things comes to mind:

- Are you selecting from the right database?
- Do you receive an timeout?



1. Yes, I have checked that (and when I simply delete the WHERE it works)
2. No, I do not receive any error or indication that something is wrong besides the fact that this returns no values.
Go to Top of Page

mallorz
Starting Member

11 Posts

Posted - 2012-09-26 : 19:29:38
I got it. I had to take out the % and use *

Access does not support %.
Go to Top of Page
   

- Advertisement -