USE test;
CREATE TABLE test.Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表
SET @i = 0;
INSERT INTO test.Num(xh) -- 写入数字辅助表
SELECT @i:=@i+1
FROM information_schema.`TABLES` a , information_schema.`TABLES` b
LIMIT 0 ,100 ;
SET @str = 'as,sbsd,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16';
-- 1
SELECT SUBSTRING( str,xh, LOCATE(',',CONCAT(@str,','),xh)-xh) AS splitstr
FROM test.Num a,
( SELECT @str AS str )b
WHERE a.xh <= LENGTH( str)
AND SUBSTRING( CONCAT(',',str),xh, 1) = ',' ;
-- 2
SET @p_DayId = '11,20,30',@p_Num = '40,50,60';
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_DayId, ',', xh), ',', -1) a,
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_Num, ',', xh), ',', -1) b
FROM Num
WHERE xh <= (LENGTH(@p_DayId)-LENGTH(REPLACE(@p_DayId,',','')) +1)
-- 经测试,以上是最快方式;利用集合处理的思想,避免了循环分割。
SELECT aa.id,aa.`mc1`, aa.type, SUBSTRING_INDEX(SUBSTRING_INDEX(aa.type, '|', xh), '|', -1) a
FROM ys.Num nn
JOIN bidw.`tv` aa
WHERE xh <= (CHAR_LENGTH(aa.type)-CHAR_LENGTH(REPLACE(aa.type,'|','')) +1)
-- 下面是mssql
USE tempdb;
CREATE TABLE Num( xh INT PRIMARY KEY(xh) );
DECLARE @xh INT ;
SET @xh = 1
WHILE @xh <=1000
BEGIN
INSERT INTO Num(xh)
SELECT @xh ;
SET @xh = @xh + 1 ;
END
DECLARE @string VARCHAR(1000);
SET @string = 'S,M,LL,XL,XXL,3XL,4XL';
SELECT t.Splitstr_
FROM ( SELECT a.xh,
SUBSTRING( str_, a.xh , charindex(',', str_+ ',' ,a.xh ) - a.xh ) AS splitstr_
FROM Num a,
( SELECT @string AS str_
) b
WHERE a.xh <= len(str_)
AND SUBSTRING( ','+str_ ,xh, 1) = ',' ) t
oracle 字符串分割版本
with a as (select '1' WWW, 'A,B,C,D' str FROM DUAL
UNION select '2' WWW, 'X,Y,Z' str FROM DUAL)
,b as (select row_number() over (order by 'xh') xh from user_objects )
SELECT www, substr( str , xh, instr(CONCAT(str,','),',', xh) -xh) AS splitstr
FROM a,
b
WHERE xh <= LENGTH( str)
AND substr( CONCAT(',',str),xh, 1) = ',' ;