iT邦幫忙

0

MSSQL字串拆解問題!!!

Hi IT前輩們~
遇到MSSQL針對TM07欄位,依序特殊符號(|| ^ |)拆解一行一行顯示出來。
請問前輩們,該怎麼下手比較好...
https://ithelp.ithome.com.tw/upload/images/20200601/200898332Tn8tPISdE.jpg
圖片中的字串如下:
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
取其中圖片一行來顯示要的結果如下:
https://ithelp.ithome.com.tw/upload/images/20200601/200898338QE2JlOSF6.jpg

0
海綿寶寶
iT邦大神 1 級 ‧ 2020-06-02 10:26:14
最佳解答

https://ithelp.ithome.com.tw/upload/images/20200602/20001787gM0qkdyxaX.png

資料來源

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;
小哈 iT邦新手 4 級 ‧ 2020-06-08 16:16:53 檢舉

感謝小魚大~
問題已解決。

0
pilipala
iT邦新手 5 級 ‧ 2020-06-02 10:20:42

參考 STRING_SPLIT

0
japhenchen
iT邦研究生 2 級 ‧ 2020-06-02 10:32:05

你的程式語言是什麼?

以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','^')
','^')

結果
https://ithelp.ithome.com.tw/upload/images/20200602/20117954c0zPVNRoEy.jpg

小哈 iT邦新手 4 級 ‧ 2020-06-08 16:17:33 檢舉

前輩,之後測試你的也OK唷。
感謝大家的幫忙~~~/images/emoticon/emoticon02.gif

我要發表回答

立即登入回答