Splitting String functions

This function splits the target string with the specified string and returns the result through the table structure.

The code in the SQL service is as follows:

1 CREATE FUNCTION [dbo]. [Fnsplit]2 (3@sInputString varchar (8000) 4, @sSplitChar varchar (Ten)5) RETURNS @tbl_List TABLE (SEQ INT IDENTITY (1,1), COD varchar (8000) )6  as7 8 BEGIN9 DECLARE @lInputStringLength Int,Ten @lPosition Int, One @lSplitChar Int A  -SET @lInputStringLength =LEN (@sInputString) -SET @lPosition =1 theSET @lSplitChar =1 -  -While @lPosition <=@lInputStringLength - BEGIN +SET @lSplitChar =CHARINDEX (@sSplitChar, @sInputString, @lPosition) -IF @lSplitChar =0 + BEGIN A INSERT @tbl_List (COD) atSELECT SUBSTRING (@sInputString, @lPosition,1+ @lInputStringLength-@lPosition) -SET @lPosition = @lInputStringLength +1 - END - ELSE - BEGIN - INSERT @tbl_List (COD) inSELECT SUBSTRING (@sInputString, @lPosition, @lSplitChar-@lPosition) -SET @lPosition = @lSplitChar +1 to END + END -      theDELETE @tbl_List WHERE RTRIM (COD) ="' *  $ RETURNPanax NotoginsengEND

return Result:

SELECT * from Dbo.fnsplit (' My future is not a dream, my heart is moving with hope ',',')

The code in Oracle is as follows:

1--The code in the type:2CREATE OR REPLACE TYPE str_split is TABLE of VARCHAR2 (4000);3 4--The code in the function:5 CREATE OR REPLACE FUNCTION fnsplit6 (7 p_inputstring in VARCHAR2,8 P_flag in VARCHAR29 )Ten RETURN Str_split One pipelined A  as -V_length Number: =LENGTH (p_inputstring); -V_start Number: =1; the v_index number; - BEGIN -while (V_start <=v_length) - LOOP +V_index: =INSTR (p_inputstring, P_flag, V_start); -  +IF V_index =0 A  Then at PIPE ROW (SUBSTR (p_inputstring, V_start)); -V_start: = V_length +1; - ELSE -PIPE ROW (SUBSTR (p_inputstring, V_start, V_index-v_start)); -V_start: = V_index +1; - END IF; in END LOOP; -  to RETURN; +END Fnsplit;

return Result:

1 Select  from (Select from Table (fnsplit) (' My future is not a dream, my heart is moving with hope ',',  ')) t)

To display rows to columns:

1 SelectA.column_value A,b.column_value b from 2(Select* from(SelectRowNum column_id,t.* fromTable (Fnsplit ('My future is not a dream, my heart is moving along with hope',',')) ( T)) A,3(Select* from(SelectRowNum column_id,t.* fromTable (Fnsplit ('My future is not a dream, my heart is moving along with hope',',')) ( t)) b4 whereA.column_id=1and b.column_id=2

Splitting String functions