1

I have the below a varchar column called "Person_Info" in DB2. I need split the its values using "_" and get the third token in DB2. Then convert it into integer.

Person_Info
----------------
Person_BILL_1234_1511011900
Person_BOB_88888
Person_MARIOSAN_10_1511011900

The output should be:

1234
88888
10

I have checked this post How to split a string value based on a delimiter in DB2. It doesn't really solve this particular case.

Have also checked this post Split a VARCHAR in DB2 to retrieve a value inside, but it requires creating an stored procedure. I can't just create a stored proc in the production environment for some utility function.

Currently, I have tried:

Select INTEGER(
    SUBSTR(
        Person_Info, LOCATE('_', Person_Info, 10) + 1, 2
    )
) from PersonTable

But it only works for "Person_MARIOSAN_10_1511011900".

So is there any solution to achieve this without creating any stored procedure? Especially if the solution is not bound to any hard coded values like 10 or 2 so that it will work in all the three examples I provided.

Community
  • 1
  • 1
Bon
  • 3,073
  • 5
  • 21
  • 40
  • Share desired output for sample you provided – Lukasz Szozda Nov 01 '15 at 09:22
  • @lad2025 updated in the answer – Bon Nov 01 '15 at 09:28
  • You could try nesting the SUBSTR and LOCATE because the underscore after the name is important AS WELL AS the underscore after the number. You are assuming that the first word ("person") will not have a length larger than 10, so if you encounter a record MARRIEDWITH_JIM_2910_1203912093 your script is in trouble again. – Herbert Van-Vliet Nov 01 '15 at 09:37
  • the first token is always fixed, the rest token can have arbitrary length – Bon Nov 01 '15 at 09:41

1 Answers1

2

I don't have access to DB2 to check it but it should work or will be very easy to adapt:

CREATE TABLE PersonTable(Person_Info VARCHAR(1000));

INSERT INTO PersonTable(Person_Info)
SELECT 'Person_BILL_1234_1511011900'  union all
SELECT 'Person_BOB_88888'  union all
SELECT 'Person_MARIOSAN_10_1511011900';

Query:

SELECT LEFT(sub.r, LOCATE('_', CONCAT(sub.r, '_'))-1) AS result
FROM (
  SELECT RIGHT(Person_Info, LENGTH(Person_Info) - 
                            LOCATE( '_',Person_Info, LOCATE('_', Person_Info)+1)) AS r
  FROM PersonTable
  ) AS sub

SqlFiddleDemo

How it works:

  1. In subquery I get the right starting from second _ character
  2. In main query I get left part to next _ character (added CONCAT to make sure it exists
  3. You can cas result to INT if needed using DB2 syntax

Assuming that you have always Person_ at the beginning:

SELECT Person_Info,
 LEFT(RIGHT(Person_Info, LENGTH(Person_Info) - LOCATE('_', Person_Info, 8)),
            LOCATE('_', CONCAT(RIGHT(Person_Info, LENGTH(Person_Info) 
                                                - LOCATE('_', Person_Info, 8))
                        ,'_')
                  )-1)
           AS result
FROM PersonTable

SqlFiddleDemo2

Output:

╔════════════════════════════════╦════════╗
║          Person_Info           ║ result ║
╠════════════════════════════════╬════════╣
║ Person_BILL_1234_1511011900    ║   1234 ║
║ Person_BOB_88888               ║  88888 ║
║ Person_MARIOSAN_10_1511011900  ║     10 ║
╚════════════════════════════════╩════════╝
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275