CODEWITHSUNDEEP
oracle-databasecasesubstr
homer
homer

Reputation: 433

Multiple CASE/WHEN with substr

NOTE: This is the final answer, ideally we shouldn't be updating original question, Thanks Alex P for pointing out.

How can we use multiple case... when with substr in Oracle?

Table (T1):
Hostname  Value
H1        oracleDB1 (local x)
H2        oracleDB2 (local y)
H3        congo/db/abc
H4        congo/db/def
H5        other/rsync

Query: *SELECT hostname, 
           CASE
           WHEN value like ('%oracle%')
           THEN substr(value, 7,instr(value, ' ')-7)  // get value = DB1, DB2..
           WHEN value like ('%congo%')
           THEN value = 'congo'                      //get value = congo
           ELSE
           substr(value, 1,5)  // get first 5 character
           END AS value
        FROM T1;*

Expected Result:
H1   DB1
H2   DB2
H3   congo
H4   congo
H5   other

I think my fist substr is wrong since it give me oracleDB1 instead of just DB1, can someone please just correct way to handle this?

Upvotes: 2

Views: 5948

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You're currently doing:

substr(value, 1, instr(value, ' ') - 1)

Which gives you the substring that starts at the first character, and is instr(value, ' '), - 1 characters long. So for oracleDB1 (local x), the first space is character 10, so you're getting the 9 characters starting at position 1.

If you just change the starting position to 7 to skip the fixed 'oracle' part:

substr(value, 7, instr(value, ' ') - 1)

then you'll the 9 characters starting at position 1, which is DB1 (LOCA. Remember the third argument is the length, it isn't the end position. So now you need to take the length of the fixed oracle part into account again, and reduce the length you're looking for by that amount too:

substr(value, 7, instr(value, ' ') - 7)

Or spelling out where the values are coming from:

substr(value, length('oracle') + 1, instr(value, ' ') - (length('oracle') + 1))

Upvotes: 3

Related Questions