-2

I have a nvarchar column which contains values like following:

item1+item2
item1+2item2
4item1+item2+2item3

I want a scalar function to calculate the item count.

As examples above, we notice:

  • items separated by "+"
  • The item may be have digit in first. This is the item count.

The required count for above examples should be as following:

item1+item2           2
item1+2item2          3
4item1+item2+2item3   7
Dale K
  • 25,246
  • 15
  • 42
  • 71
yhassany
  • 31
  • 1
  • 6
  • 3
    Fix the schema. 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?r=SearchResults&s=1|192.8696). – sticky bit May 10 '20 at 21:22

2 Answers2

0

Here is an option using try_convert() and string_split()

This one is assuming single digit leads.

Example

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 ('item1+item2')
,('item1+2item2')
,('4item1+item2+2item3')

Select * 
 From  @YourTable A
 Cross Apply (
                Select value = sum(isnull(try_convert(int,left(value,1)),1)) 
                  From string_split(SomeCol,'+') 
             ) B

Returns

SomeCol              value
item1+item2          2
item1+2item2         3
4item1+item2+2item3  7
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

You can split the values. Then extract the leading number:

select t.*, v.the_sum
from t cross apply
     (select sum(coalesce(nullif(v.num, 0), 1)) as the_sum
      from string_split(col, '+') s cross apply
           (values (try_convert(int, left(s.value, patindex('%[^0-9]%', ltrim(s.value) + ' ') - 1
                                         )
                               )
                   )
           ) v(num)
     ) v;

Here is a db<>fiddle.

Note: this assumes that the prefix is never explicitly 0. That can be handled, but adds a bit of complication that doesn't seem necessary.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786