We help IT Professionals succeed at work.

TSQL Substring

How to extact the sub string between last two "."

For example
  Str1 = "\\Myfolder\File.MyFile1.csv"
             Result should be MyFile1

  Str1 = "\\Myfolder\Folder1\subfolder2\filename.MyFile1.csv"
             Result should be MyFile1

  Str1 = "c:\Myfolder\Folder1\sub.fol.der2\fi.l.e.name.MyFile1.csv"
             Result should be MyFile1
Comment
Watch Question

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT str1,
    LEFT(ending_string, CHARINDEX('.', ending_string) - 1) AS result
FROM (
    SELECT '\\Myfolder\File.MyFile1.csv' AS str1 UNION ALL
    SELECT '\\Myfolder\Folder1\subfolder2\filename.MyFile1.csv' UNION ALL
    SELECT 'c:\Myfolder\Folder1\sub.fol.der2\fi.l.e.name.MyFile1.csv'
) AS test_data
CROSS APPLY (
    SELECT CHARINDEX('.', REVERSE(str1)) AS last_period
) AS assign_alias_names1
CROSS APPLY (
    SELECT RIGHT(str1, CHARINDEX('.', REVERSE(str1), last_period + 1) - 1) AS ending_string
) AS assign_alias_names2
awking00Information Technology Specialist

Commented:
I don't have a way to test but, in pseudo code, reverse the string and take the substring of that starting with the index of the (now first) '.' + 1, then take the substring of that up until the index of '.' -1, then reverse the result.
IT Engineer
Distinguished Expert 2017
Commented:
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(str1,1,CHARINDEX('.csv',str1)-1)),1,CHARINDEX('.', REVERSE(SUBSTRING(str1,1,CHARINDEX('.csv', str1)-1)))-1))

Open in new window

Author

Commented:
Its not always ".csv" extension but it will have file extension of 4 character including the "."
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Then you just need to replace the '.csv' with the desired extension.
awking00Information Technology Specialist

Commented:
Still have no means of testing but, trying to add syntax (assumes 3-character file extensions -
select reverse(substring(reverse(substring(str1,1,len(str1) - 4)),1,charindex('.',reverse(substring(str1,1,len(str1) - 4) - 1)))
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Did you look at the first code I posted?  It has no dependency on any specific extension.
awking00Information Technology Specialist

Commented:
Scott,
The -4 that I used to manage a 3-character extension could be substituted with additional charindex statements which you code addressed. I was just trying to reach a solution with just one select statement.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
It is one SELECT statement; the other SELECTs just provide test data, which you would replace with your own table name.

The CROSS APPLYs are part of the SELECT, and just reduce the complexity of the statement so it can be maintained later.  But, if you don't want those, then, yes, you could use Vitor's massively imbedded functions method.

Author

Commented:
Thanks