0

## MSSQL字串拆解問題!!!

Hi IT前輩們~

1.3ZTSPJR5C10|3ZTSPJR5C10||19-08-19-0232=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

### 3 個回答

0

iT邦大神 1 級 ‧ 2020-06-02 10:26:14

``````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;
``````

0
pilipala
iT邦新手 5 級 ‧ 2020-06-02 10:20:42
0
japhenchen
iT邦大師 1 級 ‧ 2020-06-02 10:32:05

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