Re: String manipulation

pativo wrote:

>Hello to all,
>
>I have small problem. I have some database fields (VARCHAR)
>and these field should hold some ahex coded values. So the
>string length must be even and each character can only be
>0-9, a-f or A-F.
>My idea was that:
>
>====8<-----------------------------------
>
>CREATE TABLE test (
> id INT4 PRIMARY KEY NOT NULL DEFAULT nextID(),
> text VARCHAR(150) NOT NULL CHECK(isAHex(text))
>);
>
>CREATE FUNCTION isAHex(VARCHAR) RETURNS BOOLEAN AS '
> DECLARE
> text_p ALIAS FOR $1;
> BEGIN
> IF ((length(text_p) % 2) <> 0) THEN
> RETURN FALSE;
> END IF;
> -- TODO How to check each character
> RETURN TRUE;
> END;
>' LANGUAGE 'plpgsql';
>
>====8<-----------------------------------
>
>Has anybody an idea how could I check each character?
>I would prefer a solution in plpgsql!!
>
>Thank!
>
>
>pativo
>
>
>
Here's one solution:
CREATE OR REPLACE FUNCTION isAHex(text) RETURNS boolean as '

DECLARE
inputText ALIAS FOR $1;
tempChar text;
isHex boolean;
BEGIN
isHex = true;
IF ((length(inputText) % 2) <> 0) THEN
return FALSE;
END IF;
FOR i IN 1..length(inputText) LOOP
tempChar := substr(inputText, i, 1);
IF tempChar ~ ''[g-z]'' THEN
return FALSE;
ELSE IF tempChar ~ ''[G-Z]'' THEN
return FALSE;
END IF;
END LOOP;

return isHex;
END;
' LANGUAGE 'plpgsql';

You may have to check the IF...ELSE IF... stuff but this should work.
I've used a very similar one to check if a value is numeric.

Ron