string split function in postgreSQL 17.01 or SQL Server2019 or mySQL 9.0 or Oracel 21c

postgreSQL 17.01 +

 
 
SELECT 'S0001' AS STU,
  unnest(string_to_array('G,E,O,V,I,N,D,U,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ',')) AS parts;
  
delete from Answer where AnswerSudentId='S0001';
drop table IF EXISTS TempSubString;
CREATE TABLE TempSubString(
id SERIAL,
studentid varchar(5),
subname VARCHAR(150),
PRIMARY KEY(id) 
);
 
insert into TempSubString(studentid,subname) 
SELECT 'S0001' AS STU,unnest(string_to_array('A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ',')) AS parts;
--  每题的得分 考虑，是否存在记录，存在删除，不存在，直接添加 Geovin Du
--  delete from  Answer where AnswerSudentId=@studentid;
insert into Answer(AnswerSudentId,AnswerQuestionId,AnswerStudentResult,AnswerScore) select studentid,id,subname,f_GetAnswerScore(id,subname) as score from TempSubString ; 
 
SELECT * FROM Answer;

　　

 

sql server 2019:

declare @studentid char(5),@result nvarchar(2000)
set @studentid=@StudentGradeId;  -- 学生编号
set @result=@resultAwsert;
insert into #TempSubString(studentid,subname)
SELECT @studentid,[value] FROM STRING_SPLIT(@result, ',');
--  每题的得分 考虑，是否存在记录，存在删除，不存在，直接添加
--  delete from  Answer where AnswerSudentId=@studentid;
insert into Answer(AnswerSudentId,AnswerQuestionId,AnswerStudentResult,AnswerScore)  select studentid,id,subname,dbo.f_GetAnswerScore(id,subname) as score from #TempSubString ;

　　

-- 测试结果
DECLARE @studentid CHAR(5),@result NVARCHAR(1000)
set @studentid='S0001';  -- 学生编号
set @result='A,C,D,B,A,D,C,B,C,D,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,C';
EXEC procImportStudentGrade @studentid,@result;

　

 

　

mysql 9.0

set @studentid='S0001';  #学生编号
set @result='A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D';
insert into temptable(studentid,subname)
SELECT @studentid,SUBSTRING_INDEX(SUBSTRING_INDEX(@result,',',help_topic_id+1),',',-1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH(@result)-LENGTH(REPLACE(@result,',',''))+1;
 
 
SELECT SUBSTRING_INDEX('A,,B,C,D',',',1);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',2);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',-2);
SELECT SUBSTRING_INDEX('A,,B,C,D',',',-1);
 
SELECT SUBSTRING_INDEX('Ann Smith', ' ', 1);
SELECT LOCATE('.', 'www.dusystem.com');
 
set @skills='MySQL,PostgreSQL,SQLite';
SELECT SUBSTRING_INDEX(@skills, ',', 1) AS skill_1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@skills, ',', 2), ',', -1) AS skill_2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@skills, ',', 3), ',', -1) AS skill_3;

　　

 

oracle 21c

DECLARE
    v_nums VARCHAR2(4000) := 'A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D';
    v_start NUMBER := 1;
    v_end NUMBER;
    v_substr VARCHAR2(4000);
BEGIN
    LOOP
        v_end := INSTR(v_nums, ',', v_start);
        EXIT WHEN v_end = 0;
        v_substr := SUBSTR(v_nums, v_start, v_end - v_start);
        DBMS_OUTPUT.PUT_LINE(TRIM(',' FROM v_substr)); -- 或者将结果插入到表中
        v_start := v_end + 1;
    END LOOP;
    -- 处理最后一个子字符串（没有逗号的情况）
    v_substr := SUBSTR(v_nums, v_start);
    DBMS_OUTPUT.PUT_LINE(TRIM(',' FROM v_substr)); -- 或者将结果插入到表中
END;
/


-- 自定义函数
CREATE OR REPLACE FUNCTION SplitString(
    p_string IN VARCHAR2,
    p_delimiter IN VARCHAR2
) 
RETURN sys.odcivarchar2list PIPELINED AS
    l_start_pos PLS_INTEGER := 1;
    l_end_pos PLS_INTEGER;
BEGIN
    LOOP
        l_end_pos := INSTR(p_string, p_delimiter, l_start_pos);

        IF l_end_pos = 0 THEN
            PIPE ROW(SUBSTR(p_string, l_start_pos));
            EXIT;
        END IF;

        PIPE ROW(SUBSTR(p_string, l_start_pos, l_end_pos - l_start_pos));
        l_start_pos := l_end_pos + 1;
    END LOOP;

    RETURN;
END;
/

-- 测试 geovindu
SELECT 
    column_value AS split_value
FROM 
    TABLE(SplitString('A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ','));

　　

 

CREATE OR REPLACE TYPE SplitStringWithSeqTableType AS TABLE OF SplitStringWithSeqType;
/

CREATE OR REPLACE FUNCTION SplitStringWithSeq(
    p_string IN VARCHAR2,
    p_delimiter IN VARCHAR2
) RETURN SplitStringWithSeqTableType PIPELINED AS
    l_start_pos PLS_INTEGER := 1;
    l_end_pos PLS_INTEGER;
    l_seq_num PLS_INTEGER := 1;
    l_str_value VARCHAR2(4000);
BEGIN
    LOOP
        l_end_pos := INSTR(p_string, p_delimiter, l_start_pos);

        IF l_end_pos = 0 THEN
            l_str_value := SUBSTR(p_string, l_start_pos);
            PIPE ROW(SplitStringWithSeqType(l_seq_num, l_str_value));
            EXIT;
        END IF;

        l_str_value := SUBSTR(p_string, l_start_pos, l_end_pos - l_start_pos);
        PIPE ROW(SplitStringWithSeqType(l_seq_num, l_str_value));

        l_start_pos := l_end_pos + 1;
        l_seq_num := l_seq_num + 1;
    END LOOP;

    -- 在PIPELINED函数中，不需要RETURN语句，因为PIPE ROW已经处理了返回。
END;
/

-- 测试
SELECT * FROM TABLE(SplitStringWithSeq('A,,B,C,D,A,B,D,B,,A,B,C,D,A,B,D,B,C,C,B,A,B,D,A,C,D,A,B,D,A,D,C,B,D,B,D,B,A,C,D,A,C,D,A,A,C,B,A,D', ','));