Another substring question in sql server

Question

Below are the data in a column of my database:

"ABC;123; TGH"
"DEF;123456; TFG"

How can i get the text "123" and "1234546" from both the data above?

Answer 1

with t as
(
SELECT 'ABC;123; TGH' C UNION ALL
SELECT 'DEF;123456; TFG'
)
SELECT
 SUBSTRING(C,CHARINDEX(';', C)+1,CHARINDEX(';', C,CHARINDEX(';', C)+1)-CHARINDEX(';', C)-1)
FROM T

Answer 2

It's almost as Microsoft went out of their way to make this hard in SQL Server. Here's one approach where each subquery strips off a column and hands the remainder to the outer query:

select  First
,       substring(Remainder, 0, PATINDEX('%;%', Remainder)) as Second
,       right(Remainder, len(Remainder) - PATINDEX('%;%', Remainder)) Remainder
from    (
        select  substring(col1, 0, PATINDEX('%;%', col1)) as First
        ,       right(col1, len(col1) - PATINDEX('%;%', col1)) as Remainder
        from    (
                select  'ABC;123; TGH' as col1
                union all
                select  'DEF;123456; TFG'
                ) sub1
        ) sub2

Answer 3

Or use ParseName trick since there are less than 4 items to be split.

;with T as
(
SELECT 'ABC;123; TGH' ColName
UNION ALL
SELECT 'DEF;123456; TFG'
)
SELECT
 PARSENAME(REPLACE(ColName,';','.'),2) as [result]
FROM T