PG_全角转半角函数

来源:互联网 发布:网络彩票2018重启时间 编辑:程序博客网 时间:2024/03/29 03:26
CREATE OR REPLACE FUNCTION convertstring(text)
  RETURNS text AS
$$
  DECLARE
   len INTEGER;
   retval text;
   tmp text;
   asciival INT4;
   difference INT4 := ascii('A')-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('A')) AND (asciival <= ascii('Z')) THEN
     SELECT asciival-difference INTO asciival;
   ELSIF (asciival >= ascii('a')) AND (asciival <= ascii('z')) THEN
     SELECT asciival-difference INTO asciival;
   ELSIF (asciival >= ascii('0')) AND (asciival <= ascii('9')) 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';
0 0
原创粉丝点击