Skip to main content

Comma Separated String to Rows in Table in SQL SERVER

Code :

DECLARE @id_list VARCHAR(MAX) = '21100010004,21100010008,21100010011'
DECLARE @table TABLE ( id VARCHAR(50) )
DECLARE @x INT = 0
DECLARE @firstcomma INT = 0
DECLARE @nextcomma INT = 0

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1 -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) )
        SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1)
        SET @x = @x - 1
    END

SELECT  RTRIM(LTRIM(id))
FROM    @table

Comments

Popular posts from this blog

show image in asp.net form from database

In order to show image from database to webform first add an image control in webpage.   <asp:Image ID="imgStudentImage" runat="server" Height="157px" Width="160px" /> Then add new webform to read image from database for example displayimage.aspx and in code behind model write the following code. public string str = //your string     public SqlConnection conn;     public SqlCommand sql = new SqlCommand();     public SqlDataReader dr;     public string strSQL;     protected void Page_Load(object sender, EventArgs e)     {         conn = new SqlConnection(str);         conn.Open();         long id = Convert.ToInt64(Request.QueryString["mId"]);         strSQL = "";         strSQL = strSQL + " SELECT snap from S...

Export Doc,Access,Image,CSV,Excel,Pdf,XML,HTML,Text,Print of Gridview in Asp.net

First import itextsharp.dll in the solution and use three namespces (basically for pdf) using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser;          Then Use the following code :- on each button  click  protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindGridDetails(GridView1);             }         }                 protected DataTable BindGridDetails(GridView GridView1)         {   ...

Advent of code 2022 day 22 part 1

  function main(input, input1) {     let grid = input.split( '\n' );     grid.shift();     // only in big input     for ( let i = 0 ; i < 100 ; i++) {         grid[i] = '                                                  ' + grid[i];     }     //console.log(grid[100])     grid = grid.map(x => x.split( '' ))     // find first left top allowed     let temppos = - 1 ;     grid[ 0 ].forEach((element, i) => {         if (temppos == - 1 && element == '.' ) {             temppos = i;         }     });     let initpos = new pos(temppos, 0 )     // console.log(initpos)     // make grid equal shape     let...