CODEWITHSUNDEEP
sqlsql-server
Ricardo Gonçalves
Ricardo Gonçalves

Reputation: 39

Get data between characters

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

Upvotes: 1

Views: 86

Answers (2)

anon
anon

Reputation:

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.)

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

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

Upvotes: 1

Related Questions