Get data between characters-CodePudding

I have files with these names in a table:

ar37_bescl_20160601_010645 (2).xml

ar37_bes_20160601_010645 (2).xml

However what interests me is just keeping the date. How can I do this throught SQL?

Result:

20160601

20160601

I've tried with substring, patindex and reverse and so far I haven't been able to.

Like this:

SELECT SUBSTRING(FILENAME,8,8), FILENAME
FROM Table

CodePudding user response：

Another way, if you can trust there is only one element in the string that represents a valid date, that also allows you to have a variable number of _ separators before or after the date:

SELECT f.fname, s.value 
FROM dbo.files AS f
CROSS APPLY STRING_SPLIT(f.fname, '_') AS s
WHERE TRY_CONVERT(date, s.value, 112) IS NOT NULL;

(Actually you may want multiple rows back if there is more than one valid date in the string.)

CodePudding user response：

If the 3rd position, here is an option using a bit of JSON.

Note: The string_escape(...,'json') is just precautionary.

Example

Select * 
      ,NewVal = JSON_VALUE('["' replace(string_escape(SomeCol,'json'),'_','","') '"]' ,'$[2]')
 From YourTable

Results

SomeCol                             NewVal
ar37_bescl_20160601_010645 (2).xml  20160601
ar37_bes_20160601_010645 (2).xml    20160601

EDIT: For a more robust approach you can use a CROSS APPLY to create the JSON string once.

Select A.SomeCol
      ,Pos1 = JSON_VALUE(JS,'$[0]')
      ,Pos2 = JSON_VALUE(JS,'$[1]')
      ,Pos3 = JSON_VALUE(JS,'$[2]')
      ,Pos4 = JSON_VALUE(JS,'$[3]')
 From YourTable A
 Cross Apply (values ('["' replace(replace(string_escape(SomeCOl,'json'),' ','_'),'_','","') '"]') ) B(JS)

Results

SomeCol                             Pos1    Pos2    Pos3        Pos4
ar37_bescl_20160601_010645 (2).xml  ar37    bescl   20160601    010645
ar37_bes_20160601_010645 (2).xml    ar37    bes     20160601    010645

CodePudding user response：

Just using regular string functions you could*, for example, do

with sampledata as (
 select 'ar37_bescl_20160601_010645 (2).xml' filename union
 select 'ar37_bes_20160601_010645 (2).xml' union
 select 'ar37_bes_20160601_010645 stuff(3).xml' 
)
select filename, Right(Left(filename, Len(filename)-CharIndex('_',Reverse(filename))),8)
from sampledata

* E&OE for edge cases