Whats wrong with this query?

I have a query:
SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE '%controlling%'
OR sp1.group_name LIKE '%contributing%'

Open in new window


that retrieves the following list:

7657567_controlling
456456_contributing
28531_controlling
090_contributing

but when I changed it to mix both filters '%_contr%', it retrieves nothing as a result... I am trying to get the very same result on the new query:

SELECT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE '%_contr%'

Open in new window

LVL 2
José PerezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
your second query should return  at least the 4 rows above.  If it doesn't then something else is happening beyond what you've shown above.


also note the underscore character is a wild card for LIKE

so %_contr%    is the same thing as %contr% except with the condition that there must be at least one character (any character) preceding contr


if you want to search for the underscore then you must escape that value
try this...

where sp1.group_name like '%\_contr%' escape '\'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
I must agree everything written above by sdstuber except the first sentence.

I cannot say the second query should return 4 rows because I don't know what values are stored in the group_name column and I don't expect they are equal to values stored in the display_name column.

Thus the only query which should work cannot use underscore in the pattern:
SELECT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE '%contr%'

Open in new window

or even
SELECT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1,
  dm_group_rp rp1
WHERE sp1.group_name LIKE 'contr%'

Open in new window

because we know there is no character before the controlling / contributing  group_name  value.

Also the last query can be optimized if the index on group_name exists. (LIKE '% ... %' cannot be optimized.)
PortletPaulEE Topic AdvisorCommented:
This is an antique way of specifying the FROM clause:

FROM dm_group_sp sp1,  dm_group_rp rp1

the danger of this old fashioned syntax is you are generating a Cartesian product of those 2 tables which may be quite unexpected.

The query you provided in the question does not reference the table dm_group_rp anywhere except in the FROM clause. So, I would simply remove it; unless of course you haven't supplied the whole query and there is a need for that table.

Then, you need to escape the underscore as identified by sdstuber.

SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE sp1.group_name like '%\_contr%' escape '\'

-----------------
nb IF sp1.group_display_name does not contain a hyphen you would get a null returned.
e.g. this query

select SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
from (select '7657567_controlling' as group_display_name from dual) sp1

returns NULL
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

pcelbaCommented:
@Paul and @sdstuber:  Why would we need to place escaped underscore into the pattern when we know the group name does not begin with such character?

If the

WHERE sp1.group_name LIKE '%controlling%'
OR sp1.group_name LIKE '%contributing%'

returned 4 rows but

WHERE sp1.group_name LIKE '%_contr%'

returned no rows then escaped underscore cannot help and the possible WHERE clause should rather be:

WHERE sp1.group_name LIKE 'contr%'
PortletPaulEE Topic AdvisorCommented:
SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE sp1.group_name like '%\_contr%' escape '\'
;

Are you saying you tried the escaped version (above) and it still returns no rows?

Personally I don't see the advantage and would just use the two strings as you were doing

SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)
FROM dm_group_sp sp1
WHERE ( sp1.group_name LIKE '%controlling%'
          OR sp1.group_name LIKE '%contributing%'
             )
;

but as you see above I also like to use parentheses. I believe I have also commented on another very similar question from you.  On that question I have used the same approach to your where clause and simplified the table joins.
PortletPaulEE Topic AdvisorCommented:
sorry @pcelba got myself confused.

the unescaped underscore is treated as a single position wildcard, so a character HAS TO exist for that location whereas % is any number (or no) characters.
José PerezAuthor Commented:
mm fom comments above.... if I use 2 'Likes':

LIKE '%controlling%' OR sp1.group_name LIKE '%contributing%'

Is the same as using 1 like that merge both?

LIKE '%contr%'

???
p.s. I am trying to decrease the time this query consumes for 3M rows database.
sdstuberCommented:
>> know the group name does not begin with such character

"need" -no, it might not be strictly necessary depending on the data, but doing that allows for exact searching for the underscore character explicitly rather than wildcarding and hoping you find it   Not "-contr",  not "contr",  not "@contr", but only "_contr".


>>> p.s. I am trying to decrease the time this query consumes for 3M rows database.  

we know, but if your results aren't correct does it matter how fast you return them?
pcelbaCommented:
I wrote "does not begin with such character"... It should have been "it begins with c character" more accurately.

LIKE 'contr%'  should be sufficient and if you create an index on group_name then it should improve the speed dramatically.
PortletPaulEE Topic AdvisorCommented:
:) I understand the intent an index for this would really help... BUT

the column group_display_name has more than we have seen.
SELECT DISTINCT SUBSTR(sp1.group_display_name, 0, instr(sp1.group_display_name,'-')-1)

& This is what is left of that column AFTER the SUBSTR

7657567_controlling
456456_contributing
28531_controlling
090_contributing

So, there is a double ended wildcard needed, as there is something before 'contr' and something after it too.

I'd rather see that combined field divided into multiple parts as separate columns.
pcelbaCommented:
Paul,

please read the question again. I agree the group_display_name column has more than we have seen BUT the WHERE clause does not use this column at all.

So I would like to know the list of possible values from group_name column. Everything we know about this column is the fact "some values start with controlling or contributing"  and  "contr does not appear at positions 2-5".

If we would use WHERE sp1.group_name LIKE 'contr%'  
then we are not sure about unwanted values on output (e.g.  contracting, contacting, etc.) if they exist.
PortletPaulEE Topic AdvisorCommented:
Ok, fair point. We agree indexing is needed.
José PerezAuthor Commented:
Thanks!
José PerezAuthor Commented:
More than happy with all your answers, thank you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.