Search code examples
db2db2-luw

Get Substrings From DB2 Column

I Have: AAAA/DATA1/Data2;xyx;pqr this data I want only:DATA1 And Data2

Solution

  • If this is for a specific row, maybe use SUBSTR? Something like

    SELECT 
  SUBSTR(column, 6, 5) AS col1
, SUBSTR(column, 13, 5) AS col2
 FROM table

    Here is something else you can do.. Although it gets pretty complicated, and this isn't the exact answer you are looking for but it will get you started. Hope this helps:

    WITH test AS (
SELECT characters
  FROM ( VALUES
  (   'AAAA/DATA1/Data2;xyx;pqr'
  ) )
    AS testing(characters)
)
SELECT 
    SUBSTR(characters, 1, LOCATE('/', characters) - 1) AS FIRST_PART
  , SUBSTR(characters, LOCATE('/', characters) + 1) AS SECOND_PART
  , SUBSTR(characters, LOCATE('/', characters, LOCATE('/', characters) + 1) + 1) 
      AS THIRD_PART
    FROM test
;

    DB2 does not have a single function for this, unfortunately. Check out this answer here: How to split a string value based on a delimiter in DB2