SQL Server substring throws error "multiple results"

Question

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.

Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Feb 19 '20 at 09:40
Sure, but it is an very old Table, probably 30-40 Years. It was desgined to fetch into Cobol Code, so i try to rewrite it the old SQL subselect,(subselect/subselect(subselect(subselect….)))) code — , Feb 19 '20 at 09:47

score 0 · Answer 1 · answered Feb 19 '20 at 09:35

0

It seems that what you want can be achieved with a simple query:

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

answered Feb 19 '20 at 09:35

Ricardo

96
4

System.Data.SqlClient.SqlException (0x80131904): Only a single expression can be specified in the select list if the subquery is not initiated with EXISTS. – Feb 19 '20 at 09:42
@4nch0r Are you sure? Because there's no subquery here – Ricardo Feb 19 '20 at 09:48
Yep, but you definitly helped me. I got another subquery, Right belong this part of Code, it can cause Problems. I should write it, iam sry ! But i tryed to Keep this thread as simple as i can. – Feb 19 '20 at 09:52

score 0 · Accepted Answer · answered Feb 19 '20 at 12:18

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.

SQL Server substring throws error "multiple results"

2 Answers2