CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Analytic Lunatic
Analytic Lunatic

Reputation: 3934

Checking for a Substring in [Field] within Case Statement for Oracle PL/SQL?

I'm writing an Oracle Database Conversion script to move records (roughly 1,300) from an old DB table to a more standardized setup with a main table and several child/reference/maintenance tables. One situation I'm dealing with is where (in the old setup) several records contain [Status] values such as RECYCLED under the [Location] field. I've gone through using a Case statement to get the basics as below:

WHEN RTRIM(LTRIM(Vendor_Name)) in (
            'EDAC') THEN 23 END as VendorID,
    CASE 
        WHEN RTRIM(LTRIM(LOCATION)) in (
            'Auctioned') THEN 3
        WHEN RTRIM(LTRIM(LOCATION)) in (
            'Recycled') THEN 5
        WHEN RTRIM(LTRIM(LOCATION)) in (
            'To Be Recycled') THEN 6
        WHEN RTRIM(LTRIM(LOCATION)) in (
            'DISPOSED OF') THEN 7
        WHEN RTRIM(LTRIM(LOCATION)) in (
            'To Be Auctioned') THEN 4

There are however a few variations with extra text (and variations OF the extra text) such as 'To be auctioned, SERVER ROOM'. I'm trying to figure out how to do something like a CONTAINS or LIKE check within my case statement, so like in the mentioned CONTAINS ('%To be auctioned%') THEN 42.

Can anyone provide an easy to understand example? I've reviewed the Oracle documentation, but I'm not fully understanding the Index portion or how exactly to specify what I'm after in proper syntax - http://www.dba-oracle.com/t_sql_contains_clause.htm.

Upvotes: 1

Views: 1897

Answers (2)

kevinskio
kevinskio

Reputation: 4551

You have to account for upper and lower case, and sometimes accented characters. é anyone?

WHEN instr(UPPER(LOCATION)),'AUCTION') > 0 THEN 4

To account for words with accents you need to use REPLACE to change é to e and then compare

Upvotes: 0

Brian McGinity
Brian McGinity

Reputation: 5935

Try: 

when location like '%To be auctioned%' then 4

Upvotes: 2

Related Questions