Hi IT前輩們~
遇到MSSQL針對TM07欄位,依序特殊符號(|| ^ |)拆解一行一行顯示出來。
請問前輩們,該怎麼下手比較好...
圖片中的字串如下:
1.3ZTSPJR5C10|3ZTSPJR5C10||19-08-19-0232=1.00
2.2AAD182R038|2AAD182R038|E1-02|S1936015620=1.00,2AAD284R03Q|2AAD284R03Q|E0-01|S1922042331=1.00,2AADLGVR03W|2AADLGVR03W|E1-04|L193503A999=1.00,2AADLHHR03U|2AADLHHR03U|E1-01|S1930064956=1.00,3ZTPCDR5940|3ZTPCDR5940||0010691233=1.00
3.^0030.20.01=True^0030.20.02=True^0030.20.03=True
4.^0030.21.01=True^0030.21.02=True
5.3TFTLCDJ04A|3TFTLCDJ04A||9D931DA1NFZZPZ190D=1.00^0030.22.01=True^0030.22.02=True
6.^0030.23.01=True
取其中圖片一行來顯示要的結果如下:
已邀請的邦友 {{ invite_list.length }}/5
DECLARE @delimiter nvarchar(255) = '|';
-- create tables
CREATE TABLE SFIS_TM (tm01 nvarchar(max), tm07 nvarchar(max));
CREATE TABLE RESULT (tm01 nvarchar(max), tm07 nvarchar(max));
-- insert data
INSERT INTO SFIS_TM VALUES ('MTJ0120F000921A2A1911G104201', '3ZTSPJR5C10|3ZTSPJR5C10||19-08-19-0232=1.00');
-- insert into RESULT
INSERT INTO RESULT
SELECT tm01, ltrim(rtrim(value))
FROM SFIS_TM
CROSS APPLY STRING_SPLIT(tm07, @delimiter)
-- output results
SELECT * FROM SFIS_TM;
SELECT * FROM RESULT;
-- delete tables
DROP TABLE SFIS_TM;
DROP TABLE RESULT;
你的程式語言是什麼？
以C#來舉例，你可以用split把這個tm7的字串值以^分解開再填格子
var splitstr = sfis_tm.tm07.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries);
int R = 1 ;
foreach(var s in splitstr){
excel.cells[R,7].Value = s;
R ++ ;
}
SQL2016以上版本用 STRING_SPLIT函數可以分解字串
2016以下..........(超麻煩)
ALTER FUNCTION [dbo].[Split](@Text NVARCHAR(200), @Delimiter CHAR(1))
RETURNS @Table TABLE(Code INT, Resule NVARCHAR(50))
AS
BEGIN
DECLARE @I AS INTEGER
DECLARE @Start AS INTEGER
DECLARE @End AS INTEGER
DECLARE @C AS CHAR(1)
DECLARE @Count AS INTEGER
SET @I = 1
SET @Start = 1
SET @End = 0
SET @Count = 1
WHILE @I < LEN(@Text)
BEGIN
SET @C = SUBSTRING(@Text, @I, 1)
IF (@C = @Delimiter)
BEGIN
SET @End = @I
END
IF @End > 0
BEGIN
INSERT INTO @Table values(@Count, SUBSTRING(@Text, @Start, @I - @Start))
SET @Start = @I + 1
SET @End = 0
SET @Count = @Count + 1
END
SET @I = @I + 1
END
INSERT INTO @Table values(@Count, substring(@Text, @Start, @I - @Start + 1))
RETURN
END
用法
select * from dbo.split('select * from dbo.split('True^0030.20.02=True^0030.20.03=True','^')
','^')
結果
iThome鐵人賽
看影片追技術