extract rows with characters in char field

[gdg1976]

hi ALL,

is there a way in sql to find a character in a column that is defined char but can contain only numbers?

example:

columnA
'001'
'002'
'R03'

I need to extract just 'R03'


THANKS in advance
bye

 

[mikrom]

Yes, you can use REGEXP_LIKE function:

with mytab(columnA) as (
  values ('001'), ('002'), ('R03')
)
select * from mytab
where REGEXP_LIKE(columnA, '[A-Za-z]')

 

[gdg1976]

hi mikrom,

thanks a lot but unfortunately my tool doesn't support REGEXP_LIKE.

thanks

 

[gdg1976]

hi mikrom,
i found this solution:

with mytab(columnA) as (
values ('001'), ('002'), ('R03')
)
select * from mytab
where length(rtrim(TRANSLATE(columnA, ' ', ' 0123456789'))) > 0


bye

 

[mikrom]

then rather this here

with mytab(columnA) as (
values ('001'), ('002'), ('R03')
)
select * from mytab
where TRANSLATE(columnA, ' ', ' 0123456789') != ''

and you save yourself two function calls

 

[mikrom]

But I'm curious because the regexp functions have been part of DB2 for a number of years: Then why do you post your questions in DB2 forums if you don't use DB2, or what is that DB2 tool that you have to use, which doesn't support all DB2 functions. In the past I knew one such tool on IBM i - it was Query/400 - and since then I have preferred to avoid using it.

 

[gdg1976]

hi,
i use several tools: in squirrel ok, it's work, in strsql ok but then in a tool named ksql it doesn't work...
So i prefer have only one select

bye and thanks