0

In SQL SERVER 2008 R2, I need to get a count of items contained in a string that can have any of the following characteristics (controlled by the user and not the system):

  • each item separated by a comma
  • sequential items summarized by the first and last item separated by a dash
  • the non-incremental character attached to only the first item in a dash separated range
  • multiple characters representing the non-incremental portion of the designation
  • combination of the above

All of the following are possible:

  • R1,R2,R3,R4
  • R1-R4
  • R1-4
  • CP10-CP12
  • R1,R15-R19,RN5

If they were all comma separated, I could just count the commas +1, but that is actually less common than the other options.

A method to count the last option should work for all options. The result should be 7

My expected approach would be to:

  1. separate the items without a dash but separated by a comma => get a count
  2. isolate the dash separated items and remove the non-incremental character(s)
  3. subtract the smaller number from the larger number and add 1
  4. Add that number to the first number for a total count

I am totally stuck on even where to begin. Any ideas?

Dhwani
  • 7,484
  • 17
  • 78
  • 139
davids
  • 5,397
  • 12
  • 57
  • 94
  • Sorry I didn't originally include that. Added it now...SQL SERVER 2008 R2 – davids May 01 '12 at 21:14
  • Can you use a CLR function instead of T-SQL? – Robert Scheibler May 01 '12 at 21:15
  • I am not sure what the difference is between a user defined function and a CLR function. Given that the functions I know use T-SQL, I am guessing that it is something different. Please elaborate. If they are the same, then, yes I can use a function, but I still don't know how to execute it via function. – davids May 01 '12 at 21:22
  • A CLR Function is an object inside an instance of SQL Server that is programmed in an assembly created in the .NET framework. Basically, you would program your method in C# or VB.NET and then import the assembly into SQL Server. The database can then use that function as it would any other user defined function. – Robert Scheibler May 01 '12 at 21:34

2 Answers2

1

You can use a trick to count the number of commas in a comma separated list:

select len(str) - len(replace(str, ',', '')

For the complete solution, you need need to do something more complicated. Long ago, I downloaded a function called split, that takes a delimited string and returns the components as if it were a table. In fact, it looks like I picked this up from here . . . T-SQL: Opposite to string concatenation - how to split string into multiple records.

So, the idea is that you split the string, and then parse the components to count. If thee is no hyphen, count "1". If there is a hyphen, you need to parse the string to get the count.

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

This can be cleaned up/optimized and is intentionally verbose but should get you started. Notably, the logic inside the last IF is almost identical to that of the WHILE and the block that gets the numeric value of the left/right elements is repeated four times.

declare @input varchar(max)
set @input = 'R1,R15-R19,RN5-RN6'

select @input

declare @elements table
(
    Element varchar(10),
    [Count] int
)

declare @element varchar(10)
declare @index int
declare @count int
declare @left varchar(10)
declare @right varchar(10)
declare @position int

while (len(@input) > 0 and charindex(',', @input) > 0)
begin
    set @element = substring(@input, 0, charindex(',', @input))
    if (charindex('-', @element) > 0)
    begin
        set @index = charindex('-', @element)
        set @left = left(@element, @index - 1)
        set @right = substring(@element, @index + 1, len(@element) - len(@left))

        set @position = 0
        while (isnumeric(substring(@left, @position, 1)) = 0)
        begin
            set @position = @position + 1
        end
        set @left = substring(@left, @position, len(@left))

        set @position = 0
        while (isnumeric(substring(@right, @position, 1)) = 0)
        begin
            set @position = @position + 1
        end
        set @right = substring(@right, @position, len(@right))

        set @count = cast(@right as int) - cast(@left as int) + 1
    end
    else
    begin
        set @count = 1
    end
    insert into @elements select @element, @count
    set @input = replace(@input, @element + ',', '')
end

if (len(@input) > 0)
begin
    set @element = @input
    if (charindex('-', @element) > 0)
    begin
        set @index = charindex('-', @element)
        set @left = left(@element, @index - 1)
        set @right = substring(@element, @index + 1, len(@element) - len(@left))

        set @position = 0
        while (isnumeric(substring(@left, @position, 1)) = 0)
        begin
            set @position = @position + 1
        end
        set @left = substring(@left, @position, len(@left))

        set @position = 0
        while (isnumeric(substring(@right, @position, 1)) = 0)
        begin
            set @position = @position + 1
        end
        set @right = substring(@right, @position, len(@right))

        set @count = cast(@right as int) - cast(@left as int) + 1
    end
    else
    begin
        set @count = 1
    end
    insert into @elements select @element, @count
end

select * from @elements
select sum([Count]) from @elements

Outputs the following results:

R1,R15-R19,RN5-RN6

R1      1
R15-R19 5
RN5-RN6 2

8
David Peden
  • 17,596
  • 6
  • 52
  • 72