Hanno,


tdmfguru schrieb:

> Hi,
>
> I would like to strip phone numbers (with a trigger after insert in a
> second field) so I get only the raw numbers in the second field. Any
> nondigits should be stripped.
> Example: +49/6849-22415 should give 49684922415.
> Is there any posibility to solve this without creating a UDF?
>
> Thanks in advance for any ideas.

if you're collecting SPs for this, here's another one ;-)

CREATE PROCEDURE SP_DIGITSONLY (
TELNO VARCHAR(40))
RETURNS (
TELDIGIT VARCHAR(40))
AS
DECLARE VARIABLE II INTEGER;
DECLARE VARIABLE PFX VARCHAR(40);
BEGIN

TELDIGIT = '';
PFX = '';

II = 1;
WHILE ( II <= 40 ) DO
BEGIN
IF ( TELNO LIKE PFX || '1%' ) THEN TELDIGIT = TELDIGIT || '1';
ELSE IF ( TELNO LIKE PFX || '2%' ) THEN TELDIGIT = TELDIGIT || '2';
ELSE IF ( TELNO LIKE PFX || '3%' ) THEN TELDIGIT = TELDIGIT || '3';
ELSE IF ( TELNO LIKE PFX || '4%' ) THEN TELDIGIT = TELDIGIT || '4';
ELSE IF ( TELNO LIKE PFX || '5%' ) THEN TELDIGIT = TELDIGIT || '5';
ELSE IF ( TELNO LIKE PFX || '6%' ) THEN TELDIGIT = TELDIGIT || '6';
ELSE IF ( TELNO LIKE PFX || '7%' ) THEN TELDIGIT = TELDIGIT || '7';
ELSE IF ( TELNO LIKE PFX || '8%' ) THEN TELDIGIT = TELDIGIT || '8';
ELSE IF ( TELNO LIKE PFX || '9%' ) THEN TELDIGIT = TELDIGIT || '9';
ELSE IF ( TELNO LIKE PFX || '0%' ) THEN TELDIGIT = TELDIGIT || '0';

PFX = PFX || '_';
II = ii + 1;
END
END

Luc.