CODEWITHSUNDEEP
sqlsql-serverstringt-sqlazure-sql-database
Julaayi
Julaayi

Reputation: 499

Extract string between two characters in a string

I have a set of strings that has datetime values and I would like to extract them. I am not sure if this is even possible using T-SQL. 

CREATE TABLE #Temp (
BLOB_NM VARCHAR(100)
);

INSERT INTO #Temp
SELECT 'products_country_20200528102030.txt'
UNION ALL
SELECT 'products_territory_20190528102030.txt'
UNION ALL
SELECT 'products_country_2020-05-20_20200528102030.txt'
;

Expected Results:

20200528102030
20190528102030
20200528102030

Upvotes: 0

Views: 2773

Answers (3)

Esperento57
Esperento57

Reputation: 17472

i suppose :

  • Files extension are not always 3 of characters length
  • Your Date/Time format are always on 14 characters

Try this :

select 
CONVERT(DATETIME, STUFF(STUFF(STUFF(left(right(BLOB_NM, charindex('_', reverse(BLOB_NM) + '_') - 1), 14),13,0,':'),11,0,':'),9,0,' ')) as Result
from #Temp

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

If interested in a helper function... I created this TVF because I was tiered of extracting portions of strings (left, right, charindex, reverse, substing, etc)

Example

Select * 
 From  #Temp A
 Cross Apply [dbo].[tvf-Str-Extract](Blob_NM,'_','.') B

Returns

BLOB_NM                                         RetSeq  RetVal
products_country_20200528102030.txt             1       20200528102030
products_territory_20190528102030.txt           1       20190528102030
products_country_2020-05-20_20200528102030.txt  1       20200528102030

The Function if Interested

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
            Cross Apply XMLData.nodes('x') AS B(i)
          ) C1
    Where charindex(@Delim2,RetVal)>1

)

Upvotes: 1

GMB
GMB

Reputation: 222482

For this dataset, string functions should do it:

select blob_nm, substring(blob_nm, len(blob_nm) - 17, 14) res from #temp

The idea is to count backwards from the end of the string, and capture the 14 characters that preced the extension (represented by the last 4 characters of the string).

Demo on DB Fiddle:

blob_nm                                        | res           
:--------------------------------------------- | :-------------
products_country_20200528102030.txt            | 20200528102030
products_territory_20190528102030.txt          | 20190528102030
products_country_2020-05-20_20200528102030.txt | 20200528102030

Upvotes: 1

Related Questions