全形轉半形函式

---全形轉半形函式如下

CREATE OR REPLACE FUNCTION convertstring(text)

 RETURNS text AS

$$

 DECLARE len INTEGER;

 DECLARE retval text;

 DECLARE tmp text;

 DECLARE asciival INT4;

 BEGIN

   --字串字元個數

   SELECT textlen($1) INTO len;

   SELECT '' INTO retval;

   --迴圈

   FOR i IN 1..len LOOP

     --取單個字元

     SELECT substring($1, i, 1) INTO tmp;

     SELECT ascii(tmp) INTO asciival;

       BEGIN

         --全形轉半形

         IF (asciival = 12288) THEN  --空格

           asciival = 32;

         END IF;

         IF (asciival > 65280) AND (asciival < 65375) THEN

           SELECT asciival-65248 INTO asciival;

         END IF;

       END;

     SELECT chr(asciival) INTO tmp;

     SELECT textcat(retval, tmp) INTO retval;

   END LOOP;

   RETURN retval;

 END;

 $$

   LANGUAGE 'plpgsql'