Click here to Skip to main content
13,258,055 members (47,626 online)
Rate this:
Please Sign up or sign in to vote.
Hi All,

Iam having list of value like this


If I execute a function i want a table like this

Number  IntegerFromList
1       CHOPL
2       NAKRL
3       NLGD2
4       DEVKD
5       HALYA

Can anyone help me to write a function for this scenario
Posted 15-Aug-12 21:36pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

// Your values as a string
string values = "CHOPL,NAKRL,NLGD2,DEVKD,HALYA";
// Split your string in to an array of values
string[] arrValues = values.Split(',');
// Connect to database using SqlConnection()
using (SqlConnection cn = new SqlConnection("connectionstring"))
    // SqlCommand to execute your sql-query
    SqlCommand cmd = new SqlCommand("INSERT INTO Table (Columne) VALUES (@Data)", cn);
    // Open connection and let it stay open until your work is done
    // Loop the values from your string array
    foreach (var value in arrValues)
        // Clear parameters so the @data parameter is not added multiple times
        // Add the next value in your array as a parameter
        cmd.Parameters.AddWithValue("@Data", value);
    // Work is done. Close connection
kirthiga S 16-Aug-12 2:55am
Thank you..
I need this as a sql function. Is it possible
AlluvialDeposit 16-Aug-12 3:09am
Why do you want to do it as a sql-function. The problem is that there are no sql-function for splitting data. See this thread:
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Instead of writing a function to return this data, why not simply add a new table to your database where you add these values and then use that table in your application.

In my opinion hardcoded list of values are something you want to avoid.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

DECLARE @S varchar(max),
  @Split char(1),
  @X xml
SELECT @S = '1,2,3,4,5',
  @Split = ','
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

This logic will give you comma seperated value just @S input to function which is hardcoded here.
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Check this.

am inserting into temptable. If you want to directly update it to your physical table , have the number column as identity column

My stored proc:
Create PROCEDURE asp_splitstrings
(@string varchar(50))
 DECLARE @id integer = 1,
  @previous_id integer = 0,
   @value varchar(50)
create table #temp
(Number int Identity(1,1),
IntegerFromList varchar(50))
     WHILE @id > 0 
      SET @id = CHARINDEX(',',@string,@previous_id+1) 
      IF @id > 0 
      SET @value = SUBSTRING(@string,@previous_id+1,@id-@previous_id-1) 
      INSERT INTO #temp VALUES (@value) 
      SET @previous_id = @id 
      IF @previous_id < LEN(@string) 
      SET @value = SUBSTRING(@string,@previous_id+1,LEN(@string))
       INSERT INTO #temp VALUES (@value) 
select * from #temp

Execute sp now
exec asp_splitstrings 'CHOPL,NAKRL,NLGD2,DEVKD,HALYA'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 16 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100