network path query

SELECT SUBSTRING(networkstring, CHARINDEX('ICS\', networkstring) + 4,
      PATINDEX('%[^0-9]%', SUBSTRING(networkstring+'\', CHARINDEX('ICS\', networkstring) + 4, 100)) - 1)
FROM (VALUES
('\\testsql3\document\ICS\2018\201834567\abc.doc'),
('\\testsql3\document\ICS\200834567\abc.doc'),
('\\testsql3\document\ICS\2007\201834567\abc.doc'))as networkpath(networkstring)
WHERE SUBSTRING(networkstring, charindex('ICS\', networkstring) + 8, 1) LIKE '[0-9]'

from should be a table

select ...
from table name
where..

why you put the sample values in from???

can this work?


SELECT SUBSTRING(networkstring, CHARINDEX('ICS\', networkstring) + 4,
       PATINDEX('%[^0-9]%', SUBSTRING(networkstring+'\', CHARINDEX('ICS\', networkstring) + 4, 100)) - 1)
 FROM networkpath
 WHERE SUBSTRING(networkstring, charindex('ICS\', networkstring) + 8, 1) LIKE '[0-9]'

Yes.  I just used that as sample data so I could actually run the code.

You can definitely replace the "(VALUES(...) networkpath(...)" part with your own table name, as you've shown.

Here is my query, but no record returned


SELECT SUBSTRING(sourceDocPath, CHARINDEX('ICSStorage\', sourceDocPath) + 4,
        PATINDEX('%[^0-9]%', SUBSTRING(sourceDocPath+'\', CHARINDEX('ICSStorage\', sourceDocPath) + 4, 100)) - 1)
  FROM HPMG_full_ScanDocExtract_Inc36
  WHERE SUBSTRING(sourceDocPath, charindex('ICSStorage\', sourceDocPath) + 8, 1) LIKE '[0-9]'
 
This is sample data


\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\20080208\20F27A07-C484-4495-B8CB-E8C2BDCB973C.tif
\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\2018\20181108\A9C5CB9C-2D0F-4547-A947-47DCA4626913.tif

So it's not necessarily 'ICS\' but could be 'ICSanything\'?

SELECT networkstring, SUBSTRING(networkstring, ICS_Start, PATINDEX('%[^0-9]%',
    SUBSTRING(networkstring+'\', ICS_Start, 100)) - 1)
FROM (VALUES
('\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\20080208\20F27A07-C484-4495-B8CB-E8C2BDCB973C.tif'),
('\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\2018\20181108\A9C5CB9C-2D0F-4547-A947-47DCA4626913.tif')
)as networkpath(networkstring)
CROSS APPLY (
    SELECT CHARINDEX('\', networkstring, CHARINDEX('ICS', networkstring)) + 1 AS ICS_start
) AS alias1
WHERE SUBSTRING(networkstring, ICS_start + 4, 1) LIKE '[0-9]'

here is the query I run

 

 SELECT sourceDocPath, SUBSTRING(sourceDocPath, ICS_Start, PATINDEX('%[^0-9]%',
     SUBSTRING(sourceDocPath+'\', ICS_Start, 100)) - 1)
 FROM HPMG_full_ScanDocExtract_Inc36
 CROSS APPLY (
     SELECT CHARINDEX('\', sourceDocPath, CHARINDEX('ICS', sourceDocPath)) + 1 AS ICS_start
 ) AS alias1
 WHERE SUBSTRING(sourceDocPath, ICS_start + 4, 1) LIKE '[0-9]'  
 

I got the full path returned

\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\20110618\B25CA97E-C0A5-4D79-B990-CDD88B537789.pdf                                                                                                                                                          
\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\20110618\A0AC9F2B-29A7-4002-883C-23B234DDBEFC.pdf                                                                                                                                                            
\\testsql3\NGProductionDocs\NGSourceDocs\ICSStorage\20110618\C11CD6B4-7FF2-4065-8924-0B29051FF56C.pdf

here is the query I run

 SELECT sourceDocPath, SUBSTRING(sourceDocPath, ICS_Start, PATINDEX('%[^0-9]%',
     SUBSTRING(sourceDocPath+'\', ICS_Start, 100)) - 1)
 FROM HPMG_full_ScanDocExtract_Inc36
 CROSS APPLY (
     SELECT CHARINDEX('\', sourceDocPath, CHARINDEX('ICS', sourceDocPath)) + 1 AS ICS_start
 ) AS alias1
 WHERE SUBSTRING(sourceDocPath, ICS_start + 4, 1) LIKE '[0-9]'  
 
I got the full path returned

The first column in the SELECT is the full path, so, yes, the full path will be returned, since you requested it.

What value is in the second column returned from the SELECT?

What I want from the output

1. only the folder names right after \ICSStorage\
2. If the folder names  right after \ICSStorage\ is  4 digit like 2018, 2017...etc skip entire row selection, I only want the folder name more than 4 digit like 201834567

I'll let someone else come in then, or you can delete the q and restart.

Good luck.