需要一个函数来获取表中的值列表

问题描述

大家好，

Iam具有这样的值列表

''CHOPL，NAKRL，NLGD2，DEVKD，HALYA''

如果我执行一个函数，我想要一个像这样的表

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

推荐答案

// 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();
}

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)

该逻辑将给您逗号分隔的值，只是@S输入到此处进行硬编码的函数.

This logic will give you comma seperated value just @S input to function which is hardcoded here.

这篇关于需要一个函数来获取表中的值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！