Subject | Re: [firebird-support] stripping phone numbers |
---|---|

Author | Lucas Franzen |

Post date | 2005-11-08T16:20:05Z |

Hanno,

tdmfguru schrieb:

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.

tdmfguru schrieb:

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

>

> 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.

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.