How to Trim multiple characters from a string

Tools

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|58421,1580050|58421,1580051|58421,1580052|58421,1580053|58421,1580054|58421,1580055|58421,1580057|58421,1580058|58421,1580059|58421,1580060|58421,1580061|58421,1580062|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 ;
2 REPLIES
Senior Apprentice

Re: How to Trim multiple characters from a string

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 '%|%'

Re: How to Trim multiple characters from a string

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