Daniel,


Daniel Albuschat schrieb:

> Now my actual question is, if there's any UDF or built-in function
> to strip off certain characters, like everything that's not a number
> or all whitespaces or so. Or can I build this as a stored procedure
> somehow?
> I would like to either have a redundant field filled in a trigger that holds the
> number without the stripped characters, or directly call it in the
> select statement, like
> select * from addresses where strip(telephone)="+49 5554455"
> or similar.

use an extra field to store nothing but the digits and search onb this one.

I'm not sure which UDF funtion will do it, but you can also use a
Storedproc to do so:

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

This should do the trick.


Luc.