PG_全角转半角函数

最新推荐文章于 2024-03-17 16:50:29 发布

o0wufan0o

最新推荐文章于 2024-03-17 16:50:29 发布

阅读量1.5k

点赞数

分类专栏： postgresql

本文链接：https://blog.csdn.net/o0wufan0o/article/details/41249847

版权

postgresql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE FUNCTION convertstring(text)
RETURNS text AS
$$
DECLARE
   len INTEGER;
   retval text;
   tmp text;
   asciival INT4;
   difference INT4 := ascii('Ａ')-ascii('A');
   fullCase char[] := ARRAY[ '！', '＃', '＄', '％', '＆', '（', '）', '＊', '＋', '，', '－', '．', '／', '：', '；', '＜', '＝', '＞', '？', '＠', '［', '］', '＾', '＿', '｛', '｜', '｝' ];

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 >= ascii('Ａ')) AND (asciival <= ascii('Ｚ')) THEN
     SELECT asciival-difference INTO asciival;
   ELSIF (asciival >= ascii('ａ')) AND (asciival <= ascii('ｚ')) THEN
     SELECT asciival-difference INTO asciival;
   ELSIF (asciival >= ascii('０')) AND (asciival <= ascii('９')) THEN
     SELECT asciival-difference INTO asciival;
   ELSE
FOR j IN 0..array_length(fullCase , 1 ) LOOP
    IF (asciival = ascii(fullCase[j])) THEN
    SELECT asciival-difference INTO asciival;
    END IF;
END LOOP;
   END IF;
END;
      SELECT chr(asciival) INTO tmp;
      SELECT textcat(retval, tmp) INTO retval;
    END LOOP;
    RETURN retval;
END;
$$
    LANGUAGE 'plpgsql';