0

I have CSV values stored in string as:

    StringBuilder CSValue = new StringBuilder();
    for (int i = 0; i < obj._contactNumbers.Count; i++)
    {
        CSValue.Append(obj._contactNumbers[i]);
        if (i != obj._contactNumbers.Count - 1)
        {
            CSValue.Append(",");
        }
    }

I have passed it to stored procedure (as CSValue.ToString()), now i have to pick each value and store it in cell. How should i seperate these values??

Please help me out!

Talha Majid
  • 117
  • 2
  • 12

3 Answers3

2

Since you are working with SQL Server 2008, instead of a CSV, I would actually push them into an XML document as follows:

<Values>
    <Value value="1" />
    <Value value="2" />
    <Value value="3" />
</Values>

Then, in the stored procedure, I would retrieve each value as a separate row using the following SQL:

SELECT value.value('(@value)[1]', 'INT') as 'id' 
FROM @idList.nodes('/Values/Value') as T(value)

This result can be joined with any other table using normal SQL query joins and get the desired behavior.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
2

I have a split function defined.

There are several thorough Stack Overflow threads regarding this, e.g. Split function equivalent in T-SQL?

Community
  • 1
  • 1
Julie
  • 353
  • 1
  • 8
  • 17
1

you can try this one

declare @Temp nvarchar(max), @c int, @a nvarchar(max)

declare @Temp_Table table (value nvarchar(max))

select @Temp = 'a,b,c,g,a'

select @c = CHARINDEX(',', @Temp)
while @c > 0
begin
    insert into @Temp_Table
    select LEFT(@Temp, @c - 1)

    select @Temp = right(@Temp, LEN(@Temp) - @c)
    select @c = CHARINDEX(',', @Temp)
end

select * from @Temp_Table

or you can write a table function to do this, if you need to do it often

Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Thanku, it works but if i want to add SNO as an attribute in table `@Temp_Table` so that i get the maximum number of rows present in the table, How should i serially add value in it??? – Talha Majid Oct 15 '12 at 17:21