Oracle SQL LIKE Query

Question

Im new here to StackOverflow and relatively new to IT, just learning the ropes of SQL. Please forgive me if I have posted this in the wrong location or asked a silly question.

Hoping to get some assistance with the following issue I am having:

I have an Oracle database (using Oracle SQL Developer to access it).

I am trying to execute a command and cannot seem to figure out how to extract from the database. I am trying to discover the number of departments in column (DEPTNAME) that contain the string 'computer' in their title.

I thought the following command would give me my answer, but it is just returning a value of 0:

select count(DEPTNAME)
from Department
where DEPTNAME LIKE '%comp%';

However when I use this command, I get a return of 41. This number isn't correct however.

select count(DEPTNAME)
from Department
where DEPTNAME LIKE '%c%';

I essentially just need to know the command for a the DBMS to find a random string and display the amount of times this string appears in the columns.

Your first command should return the correct result. Are you sure the names contain computer and not Computer (i.e. uppercase vs. lowercase) ? Please edit your question and add sample input (i.e. contents of your department table) — Frank Schmitt
– Frank Schmitt, Commented Aug 20, 2016 at 8:26
@FrankSchmitt - not necessarily. Even if case wasn't the issue, checking only for comp would also return competitive programming and decompression. The two solutions offered both make this point. — user5683823
– user5683823, Commented Aug 20, 2016 at 11:46

Gordon Linoff · Accepted Answer · 2016-08-20 11:18:57Z

7

In Oracle, comparisons are case-sensitive by default. You might want to try:

select count(DEPTNAME)
from Department
where lower(DEPTNAME) LIKE '%comp%';

Or, better yet:

select count(DEPTNAME)
from Department
where lower(DEPTNAME) LIKE '%computer%';

answered Aug 20, 2016 at 11:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zeina · Accepted Answer · 2016-08-20 08:44:48Z

0

try to write 'computer' full name... make sure of the upper and lower cases, and make sure it is written correctly

select count(DEPTNAME)  
from Department
where DEPTNAME LIKE '%computer%';

else '%c%' will return all the words that contain c in any place in the string.

answered Aug 20, 2016 at 8:44

Zeina

1,5912 gold badges25 silver badges34 bronze badges

Comments

B.Straat · Accepted Answer · 2016-08-22 07:24:59Z

0

Thank-you all for your replies. This gave me what I was looking for! It was a case sensitive issue:

select count(DEPTNAME)
from Department
where lower(DEPTNAME) LIKE '%comp%';

answered Aug 22, 2016 at 7:24

B.Straat

831 gold badge1 silver badge5 bronze badges

2 Comments

user330315 Over a year ago

You should accept the answer that solved your problem (e.g. Gordon's) not add the same answer again.

B.Straat Over a year ago

Thanks sorry about that

Collectives™ on Stack Overflow

Oracle SQL LIKE Query

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related