Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
352 views
in Technique[技术] by (71.8m points)

db2 luw - Get Substrings From DB2 Column

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...