SQL Server substring throws error “multiple results”

I use Microsoft SQL Server 2016.

I have a column that is called Failover and looks like his:

  $D$Failov:12345:54362:28564      
  $D$Failov:12345:
  $D$Failov:86905:45634

I want that number so I use :

select substring(failover, 10, 5) 
from dbo.f009
where failover like '$D$Failov:%'

It works fine, but if I want a second column called Account, it crashed with multiple results…

Select 
    account,
    (Select substring(failover, 10, 5) AS "1.Result"
     from dbo.f009 
     where Failover like '$D$Failov:%')
from 
    f009
where 
    Failover like '$D$Failov:%'

How to fix this ?

Is there a simple way to take the second number and third? I can do it with:

substring(failover, 16, 5), substring(failover, 22, 5)

etc etc but it don’t want to repeat myself.

Answer

You can repeat the string operations:

select substring(failover, 11, 5) as num1,
       substring(failover, 17, 5) as num2,
       substring(failover, 23, 5) as num3       
from dbo.f009
where failover like '$D$Failov:%';

You can also phrase this as a recursive CTE, if you have an indefinite number of values:

with t as (
      select * from (values ('$D$Failov:12345:54362:28564'), ('$D$Failov:12345:')) v(failover)
     ),
     cte as (
      select failover, convert(varchar(max), NULL) as acct, convert(varchar(max), stuff(failover, 1, 10, '')) as rest, 0 as lev
      from t
      union all
      select failover, left(rest, 5), stuff(rest, 1, 6, ''), lev + 1
      from cte
      where rest > ':'
     )
select failover, acct, lev
from cte
where lev > 0;

Here is a db<>fiddle.