Extract date and rig part from a datasource field

Transact-SQL
1

I want output like this. I want to extract date part and rig part in two different column.

DATASOURCE MatchPlodDate MatchRigNo
Setos Zradow plod 01-10-21 Rig 1 DS and NS.xlsm 01-10-21 Rig 1
ZNOZ PN plod 01-2-22 Rig 6 DS and NS.xlsm 01-2-22 Rig 6
Xlenuteq plod 1-11-21 Rig 2 DS and NS.xlsm 1-11-21 Rig 2
FdinSetals plod 1-1-22 Rig 3 DS and NS.xlsm 1-1-22 Rig 3 
DROP TABLE IF EXISTS #aaa_Test_hours;
	CREATE TABLE #aaa_Test_hours (
		[DataSource] VARCHAR(500) NULL
		);

INSERT INTO #aaa_Test_hours
VALUES (
	'Setos Zradow plod 01-10-21 Rig 1 DS and NS.xlsm'
	)
	,(
	'ZNOZ PN plod 01-2-22 Rig 6 DS and NS.xlsm'
	)
	,(
	'Xlenuteq plod 1-11-21 Rig 2 DS and NS.xlsm'
	)
	,(
	'FdinSetals plod 1-1-22 Rig 3 DS and NS.xlsm'
	);

	SELECT  DATASOURCE,SUBSTRING(DataSource, CHARINDEX('plod',DataSource) + LEN('plod')+1, 8) as [MatchPlodDate]
	,LEFT(DATASOURCE,CHARINDEX('DS',DataSource)-1) as [MatchRigNo]
	FROM  #aaa_Test_hours

Currently my output is coming like this, which is not correct. I don't know how I fixed my SQL code.

DATASOURCE MatchPlodDate MatchRigNo
Setos Zradow plod 01-10-21 Rig 1 DS and NS.xlsm 01-10-21 Setos Zradow plod 01-10-21 Rig 1
ZNOZ PN plod 01-2-22 Rig 6 DS and NS.xlsm 01-2-22 ZNOZ PN plod 01-2-22 Rig 6
Xlenuteq plod 1-11-21 Rig 2 DS and NS.xlsm 1-11-21 Xlenuteq plod 1-11-21 Rig 2
FdinSetals plod 1-1-22 Rig 3 DS and NS.xlsm 1-1-22 R FdinSetals plod 1-1-22 Rig 3

any help what i am doing wring in my SQL code???

2 
SELECT H.DataSource
	,CONVERT(date,
		SUBSTRING(H.DataSource, X.plod, 
				CHARINDEX(' ', H.DataSource, X.plod) - X.plod
			)
		, 1) AS Plod
	,CAST
	(
		SUBSTRING(H.DataSource, X.rig,
				CHARINDEX(' ', H.DataSource, X.rig) - X.rig
			)
	AS int) AS Rig
FROM #aaa_Test_hours H
	CROSS APPLY
	(
		VALUES
		(
			CHARINDEX('plod', H.DataSource) + 5
			,CHARINDEX('Rig', H.DataSource) + 4
		)
	) X (plod, rig);
3

@shantanu97 - Thank you for the readily consumable data you posted. It makes life a whole lot easier for those that try to help.

Here's a slightly different solution that will tolerate extra spaces, which is a common problem in such data. Here's the test data... note line 4....

   DROP TABLE IF EXISTS #aaa_Test_hours
;
GO
 CREATE TABLE #aaa_Test_hours
        (
		 [DataSource] VARCHAR(500) NULL
		)
;
 INSERT INTO #aaa_Test_hours
 VALUES  ('Setos Zradow plod 01-10-21 Rig 1 DS and NS.xlsm')
        ,('ZNOZ PN plod 01-2-22 Rig 6 DS and NS.xlsm')
        ,('Xlenuteq plod 1-11-21 Rig 2 DS and NS.xlsm')
        ,('FdinSetals plod  1-1-22  Rig  3  DS and NS.xlsm') --Extra spaces present
;
GO

@Ifor's code is good and efficient code because of the nice, simple, integer math he used but, with the test data above, notice the "silent failures" that occur in line 4 of the result below.

image
image924×181 6.91 KB

Here's a solution that will tolerate the extra spaces without the "silent failures"...

 SELECT  h.DataSource
        ,Plod = CONVERT(DATE,SUBSTRING(x.Plod,1,PATINDEX('%[0-9] %',x.Plod)))
        ,Rig  = CONVERT(INT ,SUBSTRING(x.Rig ,1,PATINDEX('%[0-9] %',x.Rig )))
   FROM #aaa_Test_hours h
  CROSS APPLY
	    (VALUES
		(
         SUBSTRING(h.DataSource,CHARINDEX('plod',h.DataSource)+5,500) 
        ,SUBSTRING(h.DataSource,CHARINDEX('Rig' ,h.DataSource)+4,500)
		)
	    ) x (Plod, Rig)
;

Results...

image
image925×180 6.98 KB

Of course, with such "string data", it would be a good idea to run the output into a "staging table" to check for date ranges as well as playing the Rig number against a lookup table to ensure that no one phat-phingered the Rig number.

4

Try

SELECT DataSource,
       LTRIM(LEFT(REPLACE(
                             SUBSTRING(DataSource, CHARINDEX('plod', DataSource) + LEN('plod') + 1, 8),
                             ' ',
                             REPLICATE(' ', 50)
                         ), 50)
            ) AS [MatchPlodDate],
       LTRIM(SUBSTRING(
                          LEFT(DataSource, CHARINDEX('DS', DataSource) - 1),
                          CHARINDEX('rig', DataSource),
                          LEN(DataSource)
                      )
            ) AS [MatchRigNo]
FROM #aaa_Test_hours;

Hope this helps

5

just exercising my noodles trying to think of a different way
stats of myself for myself

select 
   value 
from 
  (select 
      replace(replace(replace(datasource,'plod','plod$'),'Rig','$Rig'),'DS','$DS') as String 
   from 
     #aaa_Test_hours ) a 
CROSS APPLY STRING_SPLIT(a.String, '$')

image
image533×525 9.4 KB