达梦数据库字符串切割-自定义函数

川长思鸟来 2022-10-02 14:42 1610阅读 0赞

\--根据分隔符返回分隔的组数

CREATE OR REPLACE 
            FUNCTION get_StrArrayLength(STR VARCHAR(2000),SPLIT1 VARCHAR(10)) 
                    RETURN INT 
            AS 
                    V_LOCATION INT; 
                    V_START    INT; 
                    V_LENGTH   INT; 
            BEGIN 
                    STR:=LTRIM(RTRIM(STR)); 
                    V_LOCATION :=LOCATE(SPLIT1,STR); 
                    V_LENGTH  :=1; 
                    WHILE V_LOCATION <>0 
                    LOOP 
                            V_START:=V_LOCATION+1; 
                            V_LOCATION   :=LOCATE(SPLIT1,STR,V_START); 
                            V_LENGTH  :=V_LENGTH+1; 
                    END LOOP; 
                    RETURN V_LENGTH; 
            END;

\--取出根据某个字符切割后的第n个字符串

CREATE OR REPLACE 
            FUNCTION get_StrArrayStrOfIndex(STR VARCHAR(2000),SPLIT1 VARCHAR(10), 
                    INDEX1   INT) 
                    RETURN VARCHAR(2000) 
            AS 
                    V_LOCATION INT; 
                    V_START    INT; 
                    V_NEXT     INT; 
                    V_SEED     INT; 
            BEGIN 
                    STR:=LTRIM(RTRIM(STR)); 
                    V_START    :=1; 
                    V_NEXT :=1; 
                    V_SEED :=LEN(SPLIT1); 
                    V_LOCATION :=LOCATE(SPLIT1,STR); 
                    WHILE V_LOCATION  <>0 AND INDEX1 >V_NEXT 
                    LOOP 
                            V_START:=V_LOCATION+V_SEED; 
                            V_LOCATION :=LOCATE(SPLIT1,STR,V_START); 
                            V_NEXT :=V_NEXT+1; 
                    END LOOP; 
                    IF V_LOCATION=0 THEN 
                            V_LOCATION :=LEN(STR)+1; 
                    END IF; 
                    RETURN SUBSTRING(STR,V_START,V_LOCATION-V_START); 
            END;

\--调用方法

SELECT get\_StrArrayLength('8a,9c,4c,5K',',') FROM SYSDBA.SYSDUAL ;

SELECT get\_StrArrayStrOfIndex('8a,9c,4c,5K',',',3) FROM SYSDBA.SYSDUAL;

转载于:https://www.cnblogs.com/myrunning/p/4886145.html