Quick and easy SQL question

[LostHiWay]

I'm trying to find all employees who have a letter a in their name. I have no problem if they asked if it started with an A or ended with an A, Ijust can't figure out how to find if a name has an a without knowing the postion. So far...

select employee_name
from emp
where employee_name ???????

Any help?? I'm sure it's simple

 

[Mitzi]

select employee_name
from emp
where upper(employee_name) like '%A%'

Should do it.

 

[LostHiWay]

Originally posted by: Mitzi
select employee_name
from emp
where upper(employee_name) like '%A%'

Should do it.

wouldn't that just give me the employees with a letter A between the first and last letters in their name? What if their name started with a 'A' but had no other A's?

Or am I just thinking wrong?

 

[MrChad]

No, the LIKE operator uses the % character as a wild card. A % can represent one or more characters.

 

[LostHiWay]

Originally posted by: MrChad
No, the LIKE operator uses the % character as a wild card. A % can represent one or more characters.

Thanks!

 

[jonmullen]

Originally posted by: MrChad
No, the LIKE operator uses the % character as a wild card. A % can represent one or more characters.

For it to work the way he wants wouldn't it actually have to to represent 0 or more characters?

 

[MrChad]

Originally posted by: jonmullen

Originally posted by: MrChad
No, the LIKE operator uses the % character as a wild card. A % can represent one or more characters.

For it to work the way he wants wouldn't it actually have to to represent 0 or more characters?

You are correct, and I was mistaken in my previous post. A % can represent zero or more characters. 😱

 

[Superwormy]

As a quick note, MySQL is case INSENSITIVE with LIKE searches, so you don't need the UPPER() call if it's MySQL.

PostgreSQL has an ILIKE ( case INSENSITIVE LIKE ) too, which might help if you're using PostgreSQL.

 

[Mitzi]

Originally posted by: Superwormy
As a quick note, MySQL is case INSENSITIVE with LIKE searches, so you don't need the UPPER() call if it's MySQL.

PostgreSQL has an ILIKE ( case INSENSITIVE LIKE ) too, which might help if you're using PostgreSQL.

Ahh apologies, I didn't know that, we use Oracle here 🙂