sql - TSQL How to get the 2nd number from a string -

sql - TSQL How to get the 2nd number from a string -

we have below in row in ms sql:

got event with: 123.123.123.123, event 34, brownish fox

how can extract 2nd number ie 34 reliable in 1 line of sql?

i've thought of way that's not been mentioned yet. presuming next true:

always 1 comma before sec "part" it's word "event" number in sec part you using sql server 2005+

then utilize built in parsename function meant parsing sysname datatype.

--variable hold illustration declare @test nvarchar(50) set @test = 'got event with: 123.123.123.123, event 34, brownish fox' select ltrim(rtrim(replace(parsename(replace(replace(@test, '.', ''), ',', '.'), 2), 'event', '')))

results:

34

parsename parses around dots, want parse around commas. here's logic of i've done:

remove existing dots in string, in case swap them empty string. swap commas dots parsename use use parsename , inquire sec "piece". in illustration gives value " event 34". remove word "event" string. trim both ends , homecoming value.

i've no comments on performance vs. other solutions, , looks messy. thought i'd throw thought out there anyway!

sql sql-server