Need a .net like function in user defined function of sql server

• nileshahwad88

  SSC Veteran

  Points: 215

  More actions

  November 1, 2017 at 11:44 pm

  #333512

  I have a .net code which i need to replicate as it is in sql server. Need a same user defined function as follows. As I am not that much expert in sql server could you please help me.

  My .net function is as follows.

  public static string GetCsv(params string[] list)    {    string comma = "";    string ret = "";    for (int i = 0; i < list.Length; i++)    {      if (!string.IsNullOrEmpty(list))      {        ret += comma + list;        comma = ",";      }    }    return ret;    }
  
  
  

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 2, 2017 at 2:43 am

  #1966351

  My C# isn't great, especially when code is given in one single (unreadable) line. My guess is that this is a Delimited String Splitter? If so, a simple Google Search would have yielded you a lot of results; there's even one on this site.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• FridayNightGiant

  SSCertifiable

  Points: 7151

  More actions

  November 2, 2017 at 5:58 am

  #1966388

  It doesn't split it creates a string from a list which each item separated by a comma. To do it in SQL check out the for xml options for concatenating strings - https://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

  PS assuming you are using .net 4.0 then there is no need for a loop just use the following:

  public static string GetCsv(params string[] list)
{
  return string.Join(",", list);
}

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 2, 2017 at 6:09 am

  #1966389

  Ahh, ok. If it's converting into a CSV then this will work (using STUFF and FOR XML PATH):

  CREATE TABLE #Sample (String varchar(10));

  GO

  INSERT INTO #Sample

  VALUES ('Red'),('Green'),('Blue'),('Yellow'),('White'),('Black');

  GO

  SELECT String

  FROM #Sample;

  GO

  SELECT STUFF((SELECT ',' + String

       FROM #Sample

       FOR XML PATH('')),1,1,'') AS CSV;

  GO

  DROP TABLE #Sample;

  GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  November 2, 2017 at 7:22 am

  #1966405

  Just adding more details as you got answers. You can't create a function that will work for everything, unless you do additional work that is not really recommended. You should understand how the XML concatenation works and Wayne Sheffield did a good job explaining it here: Creating a comma-separated list (SQL Spackle) - SQLServerCentral
  Once you understood the process, you can simply create a snippet to speed up your coding. Or you can take the one I shared here among others:
  http://www.sqlservercentral.com/articles/SSMS/138994/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2