CODEWITHSUNDEEP
sqlsql-serversubstringcharindex
GreyFox9881
GreyFox9881

Reputation: 23

Retrieve text between two delimiters

I have text that is formatted in the following naming convention: ab123_text1_text2_text3_text4_text5_.xlsx. I am attempting to extract "**text4**" block. I have attempted to make use of the substring and charindex functions. Also wondering if these results can be duplicated to retrieve text5 block. Any insight would be greatly appreciated.

I attempted to use the following code to extract the text but I am receiving the "text3" block which is not what I want. Below is the code I am using:

SELECT 
    SUBSTRING(FILENAME,
                    CHARINDEX('_',FILENAME,CHARINDEX('_',FILENAME) + 1) +1,
                    CHARINDEX('_',FILENAME,CHARINDEX('_',FILENAME,CHARINDEX('_',FILENAME) +1)+1) -
                    CHARINDEX('_',FILENAME,CHARINDEX('_',FILENAME)+1)-1) AS 'SOMETHING'

FROM mytable

If the code worked as needed, My results should look like

Results Column1:
**Text4** block

Upvotes: 0

Views: 77

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

If 2016+, you can use a bit of JSON.

If <2016, there is an XML approach as well

Example

Declare @YourTable table (SomeCol varchar(100))
Insert Into @YourTable values
 ('ab123_text1_text2_text3_text4_text5_.xlsx.')

Select A.SomeCol
     , Pos4 = JSON_VALUE(JS,'$[4]')
From  @YourTable A
Cross Apply (values ('["'+replace(string_escape([SomeCol],'json'),'_','","')+'"]') ) B(JS)

Results

SomeCol                                     Pos4
ab123_text1_text2_text3_text4_text5_.xlsx.  text4

Upvotes: 1

Related Questions