Want to get second set of string from the complete string

Transact-SQL
1

DECLARE @S VARCHAR(55) = 'abc.346.matriInternational.pdf'
SELECT SUBSTRING(@S, 1, LEN(@S) - CHARINDEX('.', REVERSE(@S)))

I want to get the second set of value from right which is 346,

i have in this column all filenames need to pick second set value and group by and check the table to see how many are duplicated with same second set value.

Thanks a lot for the helpful info.

2

Keep following string part function in database and easily get any part of string. http://www.connectsql.com/2011/02/sql-server-simple-method-to-get.html

3

If this is truly a one-off, this will do the trick:

DECLARE @S VARCHAR(55) = 'abc.346.matriInternationalpdf'
DECLARE @start INT
     , @end INT;

SET @start = charindex('.', @s)
SET @end = charindex('.', @s, @start + 1)

SELECT SUBSTRING(@S, isnull(@start, 0) + 1, isnull(nullif(@end, 0), len(@s) + 1) - @start - 1)
4

@lionofdezert, The method you linked to is really not a very good way to do the job, except for very small inputs. There is a way that beats just about anything else, found here: Tally OH! An Improved SQL 8K “CSV Splitter” Function

5

It it has always four parts, try this

DECLARE @s VARCHAR(55) = 'abc.346.matriInternational.pdf'
select parsename(@s,3)
1 Like