Getting second set of number value from this string

1

declare @Sample table (Proj_name varchar(100))
insert @Sample select '123.234.martiinternational.pdf' union all
select '22356.2894.martiinternational.pdf' union all
select '6758.1287.papa.pdf'

SELECT * ,
STUFF(LEFT(Proj_name,CHARINDEX('-',Proj_name+'-')-1),1,CHARINDEX('.',Proj_name+'.'),'')
FROM @sample;

the above select i used when the string is like this:
'123.234-martiinternational.pdf' getting second set between dot and hyphen which is 234.

Now i have same string with dot instead of hyphen.
'123.234.martiinternational.pdf'

Has almost 40k strings, that i would liek to extract just the second set id number.

Thanks a lot for the helpful info.

2

Assuming that the pattern you have displayed holds up (Number Char Number Text)[code]declare @str varchar(100) = '123.234-martiinternational.pdf'

;with Find1stNonNumeric
as (
select
patindex('%[^0-9]%', @str) Offset1, @str OrigStr
),
Find2ndNonNumeric
as (
select
Offset1,
patindex('%[^0-9]%', right(OrigStr, len(OrigStr) - Offset1)) + Offset1 Offset2,
OrigStr
from
Find1stNonNumeric
)
select
Offset1, Offset2,
substring(OrigStr, Offset1 + 1, Offset2 - Offset1 - 1)
from
Find2ndNonNumeric[/code]

3

Alternative:

select *
      ,substring(proj_name
                ,charindex('.',proj_name+'.')+1
                ,charindex('.'
                          ,replace(proj_name,'-','.')+'.'
                          ,charindex('.',proj_name+'.')+1
                          )
                -charindex('.',proj_name+'.')-1
                )
  from @sample
;

Or get @JeffModen's DelimitedSplit8K.

4

Yet another

select 
	proj_name,
	left(col1,charindex('.',col1)-1)
from
	@sample a
	cross apply
	( 
		values ( stuff(a.proj_name,1,charindex('.',a.proj_name),'') ) 
	) b(col1);