How to split a string based on delimiter?

Question

I have a column of variable length character strings where some end in .

i.e.

EU...
EU
EU.
EEU.
EEU...

and I want to select the entire string where a . is not present, or the string before the first . so that I get:

EU
EU
EU
EEU
EEU

I am aware of the substr() function but that requires fixed positions to be given as paramaters. As you can see this is not possible. Can I insert regex into a SQL DB2 statement?

Why don't you get `EEU` as result? – juergen d Jul 11 '16 at 11:33 — juergen d, Jul 11 '16 at 11:33

score 3 · Accepted Answer · edited Jul 11 '16 at 13:24

3

You can also try this:

db2 "SELECT * from test"

TEST

EU...
EU
EU.
EEU.
EEU...

5 record(s) selected.

db2 "SELECT Replace(test, '.', '') as test from test"

1

EU
EU
EU
EEU
EEU

5 record(s) selected.

edited Jul 11 '16 at 13:24

Sanoop Surendran

3,484
4
28
49

answered Jul 11 '16 at 12:18

Mustafa DOGRU

3,994
1
16
24

score 0 · Answer 2 · edited May 23 '17 at 12:14

@Mahedi's answer set me off down the right path. I can't use CHARINDEX() but can use locate()

using this answer https://stackoverflow.com/a/19007026/1268941

a combination of substr() and locate() can be used:

substr(column_name,1,locate('.',column_name))

however because this keeps the trailing . I can use:

substr(column_name,1,locate('.',column_name)-1)

but this leads to "numeric argument of a built-in string function is out of range" because locate is returning -1 in some intances, of which substr() apprently does not accept as a position parameter.

using a case statement you can run the above logic on only cells that contain the .

select case when locate('.',column_name) > 0 then 
substr(column_name,1,locate('.',column_name)-1) else
column_name end as newcol from mytable;

you can also reduce this slightly with LEFT

select case when locate('-',column_name) > 0 then 
    left(column_name,locate('.',column_name)-1) else
    column_name end as newcol from mytable;

Somehow the first LOCATE in the second SELECT query example coded a character other than the period; changing the `'-'` to a period resulted in changed data rather than the unchanged data effect of the ELSE. — CRPence, Aug 12 '16 at 03:13

score 0 · Answer 3 · answered Jul 11 '16 at 17:30

0

SELECT translate(test, '.', ' ') as test from test

answered Jul 11 '16 at 17:30

Esperento57

16,521
3
39
45

`TRANSLATE--(--string-,--to---,--from-)` so the arguments shown above are reverse what apparently they would need to be: `translate(test, ' ', '.')` – CRPence Aug 12 '16 at 03:19

score 0 · Answer 4 · answered Aug 12 '16 at 03:08

While I acknowledge the implied replacement of all periods or returning just the character data preceding the period from the text in the OP, seems possible from the test data offered, that either of the following might be all that is necessary for the effect of nothing more than the trimming of trailing periods; per the effect would match what is shown as the expected output:
TRIM( TRAILING '.' FROM The_Column )
or
STRIP( The_Column, TRAILING, '.' )
FWiW: While the example data did not include any values wherein a period occurred earlier in the string, there also was no explicit mention that there always would be none, which is reflected in the sample data. If so, then the above trimming scalars are probably not helpful; but the following setup includes a few values with periods before the end of the string, to at least consider what the desired effects might be:

create table       eudata (eu_column varchar(12) )
;
insert into  qtemp.eudata values
   ( 'EU...'   )               
,  ( 'EU'      )               
,  ( 'EU.'     )               
,  ( 'EEU.'    )               
,  ( 'EEU...'  )               
,  ( 'EU.S.A..')               
,  ( '.EUROPE' )               
,  ( '.EURO..' )               
;
select eu_column                                      
     , TRIM( TRAILING '.' FROM  eu_Column ) as trim_t 
     , STRIP(  eu_Column, TRAILING, '.' )   as strip_t
from eudata 
; -- report from above query follows:
EU_COLUMN     TRIM_T        STRIP_T
EU...         EU            EU     
EU            EU            EU     
EU.           EU            EU     
EEU.          EEU           EEU    
EEU...        EEU           EEU    
EU.S.A..      EU.S.A        EU.S.A 
.EUROPE       .EUROPE       .EUROPE
.EURO..       .EURO         .EURO  
-- End of data --

How to split a string based on delimiter?

4 Answers4

TEST

1