On 2/16/11 1:29 PM, Koester, Michael wrote:
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.
DIGITS has no means to ignore non-numeric data when the operand is a
character string, because the effect is an implicit CAST of the
character field\expression to a numeric; the same as having asked
explicitly for:
DIGITS( CAST( field1 as DECIMAL(63, 31) ) )
It is because CASTing from character to numeric has no means to
recognize anything but a decimal number that the sqlstate 22018 "invalid
character for cast [to numeric]" occurs. That decimal number visible in
a string must be in the form of, consecutively, the characters:
- optional preceding blanks
- an optional negative sign "-"
- zero to 63 digits "0" to "9"
- an optional decimal separator\point "," or "." [as limited by
effective locale?]
- zero to 63 minus X digits where X is the number of digits
preceding the decimal point
- optional trailing blanks
Note: Depending on options, the digit limit may be 31 versus 63.
The following are some variations to achieve the result of having all
of the digits, but only the digits, extracted consecutively from the
string; a value which should be able to be CAST to an [big] integer or a
decimal with zero scale. Note: The CTE variations are written in that
manner solely to see the transitional data via change to the final
select to name which CTE from which to SELECT data; the expressions
could be combined completely or into several steps in a function.
<code>
create table qtemp/pn (pn char(13))
;
insert into qtemp/pn values
('(503)555-1212'),('011-17-4-1432')
,('[]--- | '),('no phone nbr.')
,('{503}555=1212'),('011\17.4.1432')
,(' '),('N/A ')
,(x'41FAC1FBD5FC'),('abcdefghijklm')
;
with
t1 (t, pn) as (select translate(pn,' ','0123456789'), pn from pn )
,t2 (t, pn) as (select land(t, repeat(x'BF', 13)) , pn from t1 )
,t3 (t, pn) as (select xor (t, repeat(x'FF', 13)) , pn from t2 )
,t4 (t, pn) as (select land(t, pn ) , pn from t3 )
,t5 (t, pn) as (select replace(t, x'00', x'40' ) , pn from t4 )
,t6 (t, pn) as (select replace(t, ' ', '' ) , pn from t5 )
select (t), (pn) from t6 /* parentheses for change to HEX() */
;
with
t1 (t, pn) as (select translate(pn,x'4F','0123456789'), pn from pn )
,t2 (t, pn) as (select xor (t, repeat(x'FF', 13)) , pn from t1 )
,t3 (t, pn) as (select land(t, pn ) , pn from t2 )
,t4 (t, pn) as (select lor (t, repeat(x'40', 13)) , pn from t3 )
,t5 (t, pn) as (select replace(t, ' ', '' ) , pn from t4 )
select (t), (pn) from t5 /* parentheses for change to HEX() */
;
select
replace(
translate(pn, ' '
, x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F
202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F4041
42434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F60616263
6465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485
868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7
A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9
CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEB
ECEDEEEFFAFBFCFDFEFF') /* xlate all but x'F0' to x'F9' to blanks */
, ' ', '') /* replace all blanks with the null\empty string */
from pn
;
drop function GetDigits
;
create function GetDigits
( inpchr varchar(128) ccsid 37
) returns varchar(66) ccsid 37
language sql deterministic
begin
declare outchr varchar(66) ccsid 37 default '';
declare chrpos smallint default 1;
while chrpos < length(inpchr) do
if substr( inpchr, chrpos, 1 ) between x'F0' and x'F9' then
set outchr = outchr concat substr( inpchr, chrpos, 1 );
end if;
set chrpos = chrpos + 1;
end while;
return outchr;
end
;
select getdigits(pn) from pn
;
</code>
Regards, Chuck
midrange.com
