SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test
( id int IDENTITY(1,1) PRIMARY KEY
, string varchar(200)
);
INSERT INTO test (string)
VALUES
  ('1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1')
, ('2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0');
2 rows affected

SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY (SELECT NULL)) AS field, v.*
FROM test t
CROSS APPLY STRING_SPLIT(t.string, ' ') v;


id
string
field
value


1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
1
1

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
2
08/21/2024

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
3
8

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
4
764

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
5
200

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
6
10

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
7
312360171230

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
8
20

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
9
08/20/2024

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
10
21

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
11
N

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
12
15TH

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
13
ST

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
14
YAWEFAS

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
15
PHOENIX

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
16
AZ

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
17
22

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
18
548666

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
19
63

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
20
19:34:35

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
21
1

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
1
2

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
2
08/22/2024

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
3
9

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
4
875

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
5
300

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
6
20

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
7
423471282341

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
8
21

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
9
08/21/2024

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
10
22

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
11
S

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
12
16TH

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
13
ST

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
14
SAFEWAY

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
15
PHOENIX

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
16
AZ

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
17
23

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
18
548667

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
19
64

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
20
20:45:46

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
21
0


/*
If the string always follows the same pattern 
and your dates are always in the same ordinal position split by ' '
you can use this method to return the fields you need based on "field number".
*/
SELECT *
FROM 
(
  SELECT t.*
       , ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY (SELECT NULL)) AS field
       , v.*
  FROM test t
  CROSS APPLY STRING_SPLIT(t.string, ' ') v
) splt
WHERE splt.field IN (2, 9);


id
string
field
value


1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
2
08/21/2024

1
1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1
9
08/20/2024

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
2
08/22/2024

2
2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0
9
08/21/2024

id	string	field	value
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	1	1
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	2	08/21/2024
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	3	8
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	4	764
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	5	200
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	6	10
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	7	312360171230
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	8	20
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	9	08/20/2024
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	10	21
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	11	N
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	12	15TH
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	13	ST
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	14	YAWEFAS
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	15	PHOENIX
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	16	AZ
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	17	22
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	18	548666
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	19	63
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	20	19:34:35
1	1 08/21/2024 8 764 200 10 312360171230 20 08/20/2024 21 N 15TH ST YAWEFAS PHOENIX AZ 22 548666 63 19:34:35 1	21	1
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	1	2
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	2	08/22/2024
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	3	9
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	4	875
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	5	300
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	6	20
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	7	423471282341
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	8	21
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	9	08/21/2024
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	10	22
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	11	S
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	12	16TH
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	13	ST
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	14	SAFEWAY
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	15	PHOENIX
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	16	AZ
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	17	23
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	18	548667
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	19	64
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	20	20:45:46
2	2 08/22/2024 9 875 300 20 423471282341 21 08/21/2024 22 S 16TH ST SAFEWAY PHOENIX AZ 23 548667 64 20:45:46 0	21	0