blogger

    blogger
  • hot log

  • comment

    twitter

    • 空间续费,加上ssl连接。。

      2018-11-22 01:04

    • 12.10.xC6出来时候碰到"Could not determine encryption mode from page zero",竟果然是BUG!http://www-01.ibm.com/support/docview.wss?rs=630&uid=swg21973408

      2015-12-21 20:15

    • 空间已经转移到新加坡,希望会好一些。

      2015-11-16 14:34

    • 增加新域名liaosnet.cc,与liaosnet.com同指向。

      2015-10-09 15:25

    • 空间迁移到香港。或许速度会快一点。。

      2014-02-13 20:10

    • More»

    blog sort

    links

    record

    banner

    GBase 8s 自定义split_part函数

    暗夜星空's Memory from Mar 6 , 2019 at 17:18 PM , under Category:IFX/GBase
    GBase 8s 默认无split_part函数,但可以通过substring_index()函数来达到split_part的功能,具体如下:
    drop function if exists split_part2;
    
    create function split_part2( str_in lvarchar(2048), separator_in char(1), field_in int  )
      returning varchar(255) ;
    
      define str_len int;
      define pos_curr int;
      define count_field   int;
      define pos_char char(1);
    
    --set debug file to "/home/gbasedbt/temp/0311/split.deg";
    --trace on;
    
      IF field_in <= 0 THEN return null; END IF;
    
      LET count_field   = 0;
      LET str_len = length(str_in);
    
      FOR pos_curr = 1 TO str_len
        LET pos_char = substr(str_in,pos_curr,1);
        IF pos_char = separator_in THEN
          LET count_field = count_field + 1;
        END IF;
      END FOR;
    
      IF field_in > count_field + 1 THEN
        return null;
      ELSE
        return SUBSTRING_INDEX(SUBSTRING_INDEX(str_in,separator_in,field_in),separator_in,-1);
      END iF;
    --trace off;
    
    end function;                                                                                                                                                         

    或者
    create function split_part( str_in lvarchar(2048), separator_in char(1), field_in int  )
    returning varchar(255) ;
    
    define res varchar(255);
    define str_len int;
    define pos_curr int;
    define substr_start int;
    define substr_length int;
    define pos_char char(1);
    
    IF field_in <= 0 THEN return ; END IF;
    
    LET res = '';
    LET substr_start = 0;
    LET substr_length = 0;
    LET str_len = length(str_in);
    
    FOR pos_curr = 1 TO str_len
    
       LET pos_char = substr(str_in,pos_curr,1);
       IF pos_char = separator_in THEN
          LET field_in = field_in - 1;
       END IF;
    
       IF field_in = 1 and substr_start = 0 THEN
          LET substr_start = pos_curr + DECODE(pos_char,separator_in,1,0);
       END IF;
    
       IF field_in <= 0 THEN
          LET substr_length = pos_curr;
          EXIT FOR; 
       END IF;
    
    END FOR;
    
    IF substr_length = 0 THEN
       LET substr_length = str_len+1;
    END IF;
    
    IF substr_start = 0 THEN
       LET substr_start = str_len+1;
    END IF;
    
    IF substr_length < substr_start THEN
       LET substr_length = 0;
    ELSE
       LET substr_length = substr_length - substr_start;
    END IF;
    
    RETURN NVL(substring ( str_in from substr_start for substr_length ),'');
    
    end function;                                                                                                                                                           

    Post a comment:

    Your email address will not be published. Required fields are marked﹡