CODEWITHSUNDEEP
sqldb2substring
brucezepplin
brucezepplin

Reputation: 9752

How to split a string based on delimiter?

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?

Upvotes: 0

Views: 3317

Answers (4)

CRPence
CRPence

Reputation: 1259

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

Upvotes: 0

Esperento57
Esperento57

Reputation: 17462

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

Upvotes: 0

Mustafa DOGRU
Mustafa DOGRU

Reputation: 4112

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.

Upvotes: 3

brucezepplin
brucezepplin

Reputation: 9752

@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;

Upvotes: 0

Related Questions