string split function in postgreSQL 17.01 or SQL Server2019 or mySQL 9.0

geovindu

已于 2024-11-14 08:27:43 修改

阅读量430

点赞数 1

分类专栏：数据库编程文章标签： postgresql sql 数据库 mysql sqlserver

于 2024-11-09 06:56:24 首次发布

本文链接：https://blog.youkuaiyun.com/geovindu/article/details/143638792

版权

数据库编程专栏收录该内容

52 篇文章

订阅专栏



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;