?
Solved

Oracle LIKE Operator

Posted on 2014-09-02
8
Medium Priority
?
341 Views
Last Modified: 2014-09-02
I am a C#, MS SQL developer who is new to Oracle PL/SQL.

My question is probably simple to you experts.

I created a function that searches for a substring within a string and returns the substring if found or 'none' if not found.

I am calling the function in a select statement using the dual table.

The function:

CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)

RETURN VARCHAR2 AS

BEGIN

  IF phone_number LIKE '___-___-____' THEN

    --we have a phone number with an area code.

    RETURN SUBSTR(phone_number,1,3);

  ELSE

    --there is no area code

    RETURN 'none';

  END IF;

END;

/

SQL Statement:
select area_code('716') from dual ;
Returns 'none'
select area_code('716-123-456') from dual ;
Also returns 'none'

What am I missing?

Thanks,
0
Comment
Question by:Dovberman
  • 4
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 40299545
area_code('716-123-456')

your second example doesn't have 4 digits in the last portion

try this...


SELECT area_code('716-123-4567') FROM DUAL;


The underscore  for LIKE is a wildcard of exactly one character.

So ___-___-_____  means

exactly 3 characters, dash, exactly 3 characters, dash, exactly 4 characters
0
 

Author Comment

by:Dovberman
ID: 40299552
Never mind, I found the error.

Changed IF phone_number LIKE '___-___-____' THEN

to IF phone_number             LIKE '___-___-_____' THEN

Needed 4 dashes as place holders to match the pattern specified in the function.

All programming languages are picky.
0
 

Author Comment

by:Dovberman
ID: 40299576
I've requested that this question be closed as follows:

Accepted answer: 0 points for Dovberman's comment #a40299552

for the following reason:

I found my error.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Assisted Solution

by:mcsween
mcsween earned 400 total points
ID: 40299553
The LIKE operator works exactly the same as SQL.

Select area_code from dual where somefield LIKE '716%';

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 40299569
you might want to try using regular expressions then you can check for digits rather than just any characters

IF REGEXP_LIKE(phone_number, '[0-9]{3}-[0-9]{3}-[0-9]{4}') THEN
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 40299577
No, the problem wasn't the number of dashes, you already had 3-3-4

the problem was your input data didn't look like a phone number.

The first post identified the problem.

Your suggested solution actually creates an additional problem because it has 5 underscores.  

This means

123-123-12345 would be accepted as a phone number even though it's not correct

Also, you may want to use my followup post anyway since it's a more thorough check than simple wildcarding
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 40299587
The example query in

http:#a40299553

has addtional problems because it checks only for starting with 3 digits

'716%'  would match  these strings which are not correct for that function

'716'
'716-123-'
'716-scoobydoo'
0
 

Author Closing Comment

by:Dovberman
ID: 40299897
It was not as simple or complete as I had anticipated.

Thanks for the additional analyses.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month16 days, 22 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question