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