Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi All,
 
Iam having list of value like this
 
'CHOPL,NAKRL,NLGD2,DEVKD,HALYA'
 
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
good
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
    cn.Open();
 
    // Loop the values from your string array
    foreach (var value in arrValues)
    {
        // Clear parameters so the @data parameter is not added multiple times
        cmd.Parameters.Clear();
        // Add the next value in your array as a parameter
        cmd.Parameters.AddWithValue("@Data", value);
        cmd.ExecuteNonQuery();
    }
    // Work is done. Close connection
    cn.Close();
}
  Permalink  
Comments
kirthiga S at 16-Aug-12 2:55am
   
Thank you..
I need this as a sql function. Is it possible
AlluvialDeposit at 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: http://www.codeproject.com/Questions/441329/insert-rows-in-to-table-from-string
Rate this: bad
good
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.
  Permalink  
Rate this: bad
good
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.
  Permalink  
Rate this: bad
good
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))
 AS 
 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 
     BEGIN
      SET @id = CHARINDEX(',',@string,@previous_id+1) 
      IF @id > 0 
      BEGIN 
      SET @value = SUBSTRING(@string,@previous_id+1,@id-@previous_id-1) 
    
      INSERT INTO #temp VALUES (@value) 
      SET @previous_id = @id 
      END 
      END 
      IF @previous_id < LEN(@string) 
      BEGIN 
      SET @value = SUBSTRING(@string,@previous_id+1,LEN(@string))
       INSERT INTO #temp VALUES (@value) 
       END
select * from #temp

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

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

  Print Answers RSS
0 DamithSL 320
1 OriginalGriff 155
2 Peter Leow 115
3 Afzaal Ahmad Zeeshan 114
4 deepakdynamite 110
0 OriginalGriff 7,510
1 DamithSL 5,519
2 Sergey Alexandrovich Kryukov 4,994
3 Maciej Los 4,936
4 Kornfeld Eliyahu Peter 4,514


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 16 Aug 2012
Copyright © CodeProject, 1999-2014
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