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 |