Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

extract rows with characters in char field

Status
Not open for further replies.

gdg1976

Programmer
Sep 1, 2021
14
IT
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
 
Yes, you can use REGEXP_LIKE function:

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

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

thanks
 
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
 
then rather this here
Code:
with mytab(columnA) as (
values ('001'), ('002'), ('R03')
)
select * from mytab
where TRANSLATE(columnA, ' ', ' 0123456789') != ''
and you save yourself two function calls
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top