PL/SQL Parse substring and display

Hello expert,

Working to write a query that displays a new Contact name by parsing the Location_Name and writing the Contact First Name using a substring of the Location_Name.

For instance. where the  Location_Name is Walgreens #10630 would like for the Contact First name to be Location #10630

Found some info on PL/SQL SUBSTR
select SUBSTR('Walgreens #02843', 11, 8) from dual
returns
#02843

Which is the desired result.

Now the string 'Location' should be concatenated to Location_Name substring and displayed as New_FName.

This is what has been drafted so far but after some tweeking and refactor can't get it to work


Select Contact_ID,
Location_Name,
CON.Owner_ID,
CON.IS_DISABLED,
First_Name as Old_FName,
Last_Name as Old_LName,
LOCATION_NAME, 'Location' + SUBSTR(Select LOCATION_NAME, 11, 8 from dual) as New_Fname 
from CCM_Contact CON
join CCM_LOCATION LOC on LOC.LOCATION_ID = OWNER_ID
where 
owner_type_code =15002
and
Owner_id in
(
SELECT LOCATION_ID
FROM CCM.CCM_LOCATION 
where employer_id = 214782
)

Open in new window


Also included a GIF of the query output w/o the substr statement.

Thanks.

Allen in DallasContact_names_update
LVL 1
Allen PittsBusiness analystAsked:
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.

flow01Commented:
Is this what your searching for ?
Select Contact_ID,
Location_Name,
CON.Owner_ID,
CON.IS_DISABLED,
First_Name as Old_FName,
Last_Name as Old_LName,
LOCATION_NAME, 'Location' + SUBSTR(LOCATION_NAME, 11, 8) as New_Fname
from CCM_Contact CON
join CCM_LOCATION LOC on LOC.LOCATION_ID = OWNER_ID
where
owner_type_code =15002
and
Owner_id in
(
SELECT LOCATION_ID
FROM CCM.CCM_LOCATION
where employer_id = 214782
)
flow01Commented:
And if your # is not always on the same place
LOCATION_NAME, 'Location' + SUBSTR(LOCATION_NAME,  INSTR(LOCATION_NAME,'#'),8) as New_Fname
And if you always want the whole part from #
LOCATION_NAME, 'Location' + SUBSTR(LOCATION_NAME,  INSTR(LOCATION_NAME,'#')) as New_Fname
slightwv (䄆 Netminder) Commented:
Concatenation in Oracle is || not +.

I would also not use SUBSTR since the location_name can likely have variable length.

You also don't want to select from dual inside the select.

replace:
LOCATION_NAME, 'Location' + SUBSTR(Select LOCATION_NAME, 11, 8 from dual) as New_Fname

with:
LOCATION_NAME, 'Location ' || regexp_substr(LOCATION_NAME,'[#0-9]+$') as New_Fname

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
select old_fname||' '||substr(location_name,instr(location_name, '#')) new_fname;
Allen PittsBusiness analystAuthor Commented:
Thanks
johnsoneSenior Oracle DBACommented:
A note on terminology.

This is not a PL/SQL question.  PL/SQL is Oracle's procedural language for procedures/functions/triggers/etc.  This is simply a SQL question.  Using PL/SQL in the title of the question can keep some experts that could help you from looking at your question.  People that are strictly SQL experts and don't use PL/SQL may not look at your question.

Or, someone that has PL/SQL expertise can skip over it because they don't have time to decipher a lengthy procedure at the moment.
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.