RE: SQL digits trows SQLSTATE 22018

Michael,

I don't think that DIGITS is your answer. It assumes you will be beginning with a numeric to start with. You didn't include your input data, but let's assume the phone number character string is represented as: 123-456-7890.

This is not a number, so DIGITS will fail. According to the manual: "The DIGITS function returns a character-string representation of the absolute value of a number."

It seems you wish to strip the '-' character. If this is true, one way is to use the SUBSTR and LOCATE functions.

select field1, trim(substring(field1,1,locate('-',field1,1)-1) ||
substring(field1,5,locate('-',field1,4)-1) ||
substring(field1,9,4))
from testfile

I ran this against a table containing '123-456-7890' in the field1 column.

It returns:
FIELD1 TRIM function
123-456-7890 1234567890

Is this what you are after? There are probably other solutions that will flood this thread by tomorrow. :-)

steve

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Wednesday, February 16, 2011 3:29 PM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL digits trows SQLSTATE 22018

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