Making the following query in Oracle SQL developer work

[Jack_Tauson_Sr]

In Oracle SQL Developer, I have the following two queries :

Sample Data for Reference from Employee Table

COMPANY_ID	NAME								EMP_ID
1	        Steven									4
2			Steven steven is working				4
3			That work was done by steven			4
4			Bradley	is a good guy					4
5			His name is yoshio						4

Query #1:

SELECT COMPANY_ID,NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%Steven%'

The above query returns the results like the following which is perfectly fine:

COMPANY_ID |  NAME  					| EMP_ID

1             Steven 						4	
2			  Steven steven is working      4

Query #2:

The LOWER(NAME) as NAME converted all the upper case letters of the NAME column to lowercase and when I used the following
query, I didn’t not receive three rows which contains the word steven but only one as shown below:

SELECT COMPANY_ID,LOWER(NAME) as NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%steven%'

Here is the output of the above query in Oracle SQL Developer:

COMPANY_ID |  NAME  					| EMP_ID
2			  steven steven is working		4

So, basically the pattern I am noticing after running the Query #2 is that if the searchable word(in our case it is steven) is
surrounded by something on its left and right, that row is returned otherwise no other row with the searchable word is returned.
For example, from the sample data above, rows with company_id 1 and 3 containing the word steven wasn’t returned.
Could anyone tell me why it’s happening like this?

[DaveMaxwell]

I think you’re getting fooled by the alias, which you’ve given the same name as the field name (which is valid). Aliases are only only used in the returned recordset, providing a field name for the column of data. Everywhere else in the query, the alias wouldn’t apply. So in your case LOWER(NAME) is not the same as NAME. If you’d like to prove this to yourself, change the AS NAME to something like AS LOWER_CASE_NAME and change the where clause to AND LOWER_CASE_NAME like ‘%steven%’. It will give you an error.

You can see it in your example, because the only row returned DOES have steven in the text in addition to Steven.

You’ll need to apply the LOWER in the where clause instead. I’m venturing that in your case, you’re going to want the proper name capitalization anyway, so you’d change the query to

SELECT company_id
     , name
     , emp_id
  FROM employee
 WHERE emp_id = 4
   AND LOWER(name) LIKE '%steven%'