have such a coordinate parameter list: 
X, Y, D, L is a space character, before the semicolon as a set of 
Table A: 

A01 barcode how A02 parameter 
8888 X=983, Y=217 D=12 L=N; X=983, Y=517 D=12 L=N; X=944, Y=100 D=12 L=N; X=944, Y=634 D=12 L=N; 
9999 X=983, Y=239 D=12 L=N; X=983, Y=539 D=12 L=N; X=944, Y=100 D=12 L=N; 
. 

Now wants to generate the structure of the table, according to the division of bar code generated five columns, table structure is: 

Table B: 

Barcode, X, Y, D, L 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
12, 8888, 983, 217 N 
12, 8888, 983, 517 N 
12, 8888, 994, 100 N 
12, 8888, 994, 634 N 
12, 9999, 983, 239 N 
12, 9999, 983, 539 N 
12, 9999, 944, 100 N 

CodePudding user response:

STRING_SPLIT function of 2012 began, if does not have to write a separate function
The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T ([A01 barcode] NVARCHAR (20), [] how A02 parameters NVARCHAR (128)) 
Insert # T 
Select '8888', N 'X=983, Y=217 D=12 L=N; X=983, Y=517 D=12 L=N; X=944, Y=100 D=12 L=N; X=944, Y=634 D=12 L=N; 'union all 
Select '9999', N 'X=983, Y=239 D=12 L=N; X=983, Y=539 D=12 L=N; X=944, Y=100 D=12 L=N; '
Go 
- the end of the test data 

The SELECT A01 barcode, 
(
SELECT the REPLACE (t2) value, 'X=', ') FROM STRING_SPLIT (t.v alue, ' ') t2 WHERE t2. The value LIKE '% X=' 
) AS X, 
(
SELECT the REPLACE (t2) value, 'Y=', ') FROM STRING_SPLIT (t.v alue, ' ') t2 WHERE t2. The value LIKE '% Y=' 
) AS Y, 
(
SELECT the REPLACE (t2) value, 'D'=', ') FROM STRING_SPLIT (t.v alue, ' ') t2 WHERE t2. The value LIKE '% D=' 
) AS D, 
(
SELECT the REPLACE (t2) value, 'L=', ') FROM STRING_SPLIT (t.v alue, ' ') t2 WHERE t2. The value LIKE '% L=' 
) AS L 
The FROM # T 
CROSS the APPLY 
(SELECT * FROM STRING_SPLIT ([] how A02 parameters, '; T 
 '))WHERE t.v alue & lt;> "';