Re: SQL digits trows SQLSTATE 22018

havn't not fully tested. But try this out:
select replace(translate(field1,' ','(-)/'), ' ',''),
replace(translate(field2,' ','(-)/'), ' ',''),
into :target1,
:target2
From fileA
Where keyfield1 = :TheDesiredRecord;



"Koester, Michael" <mkoester@xxxxxxxxxxxxx> wrote in message
news:mailman.29234.1297892046.2702.rpg400-l@xxxxxxxxxxxx...
I have an embedded SQL statement which I'm trying to use to parse phone
numbers from a few 13-character fields. What I have isn't working if
any one of the fields has no digits at all in it and throws SQLSTATE
'22081'. I guess I'm not too proficient yet with the DIGITS function,
so maybe someone can set me on the right track?

A simplified version would be as follows:

Exec sql
select substr(digits(field1),23,10),
substr(digits(field2),23,10)
into :target1,
:target2
From fileA
Where keyfield1 = :TheDesiredRecord;

The database fields (field1 and field2) are defined 13 A; the receivers
(target1 and target2) are both 10 A.
If all goes well, and field1 and/or field2 contain 7 or 10 digits, with
or without dashes, slashes, or other creative commentary, my receivers
would get the numerals only, or blanks if no digits exist in the
associated database field.

Of course, sqlstate 22018 keeps that from happening. Suggestions
welcome.

Thanks.
Michael Koester