All Forums Tools
Juanetta 2 posts Joined 03/16
31 Mar 2016
How to Trim multiple characters from a string

Hello
 
I found this code on your forun but need help to tweek it a bit:\  
My list has two delimiters Comma (,) and Line (|) (see below)

1580046|58421,1580047|58421,1580048|58421,1580049|5842 1,1580050|58421,1580051|58421,1580052|58421,1580053|58 421,1580054|58421,1580055|58421,1580057|58421,1580058| 58421,1580059|58421,1580060|58421,1580061|58421,158006 2|58421,1580063|58421,1580064|58421,1*

I need the data before the (|) and omit the other data, so the final data rows will look like this:  Any help tweeking the code below would be helpful. thanks

1580046
1580047
1580048
1580049
1580050
1580051
1580052
1580053
1580054
1580055
1580057
1580058
1580059
1580060
1580061
1580062
1580063
1580064

 

WITH RECURSIVE SPLIT_ONE_TO_MANY (POS,SEQ, NEW_STRING, REAL_STRING) AS
(
SELECT
  0,0, CAST('' AS VARCHAR(100)),TRIM( order_quesn_list)
FROM cl_otl_1
where  cl_otl_1.OTL_ID ='1509136706'
UNION ALL
SELECT
  CASE WHEN POSITION('|' IN REAL_STRING) >0 
    THEN POSITION('|' IN REAL_STRING)
    ELSE CHARACTER_LENGTH(REAL_STRING)
  END DPOS,
  SEQ + 1,
  TRIM( both '|' FROM SUBSTR(REAL_STRING, 0, DPOS )),
  TRIM(SUBSTR ( REAL_STRING, DPOS +1 ))
FROM SPLIT_ONE_TO_MANY
WHERE DPOS > 0 
)
SELECT *
FROM SPLIT_ONE_TO_MANY
WHERE SEQ > 0 ;

 

 

dnoeth 4628 posts Joined 11/04
01 Apr 2016

Recursion is quite inefficient for tokenizing a string, better use STRTOK:

WITH cte (inkey, str) AS
 ( 
   SELECT OTL_ID
      , order_quesn_list
   FROM cl_otl_1
   WHERE  OTL_ID ='1509136706'
 ) 
SELECT
   STRTOK(token, '|', 1) -- split into columns based on '|' 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(cte.inkey, cte.str, ',') -- split into rows based on ','
            RETURNS (outkey INTEGER
                    ,tokennum INTEGER
                    ,token VARCHAR(20) CHARACTER SET UNICODE)
           ) AS d           
WHERE token LIKE '%|%'

 

Dieter

Juanetta 2 posts Joined 03/16
01 Apr 2016

THANK YOU!!!! DNOETH. This is very helpful.....

You must sign in to leave a comment.