CODEWITHSUNDEEP
sql-serversql-server-2005
user57508
user57508

Reputation:

How do I work with a comma-separated-string in SQL Server

Hy there!

So we have some kind of array in our domain.

eg [ 123, 234, 345 ]

When we are going to query this against SQL Server, we usually transform this array to 123,234,345 (string), so that we can do something like (with string replace):

SELECT    *
FROM      [dbo].[myTable]
WHERE     [myColumn] IN (123,234,345)

This is pretty straight forward, even if a WHERE [myColumn] = 123 OR ... would be better when it comes to performance.

This (the way of transforming) is the only way to get the array from the domain to the database.

Now I need, to do a join, this array to be transformed to a table. The first thing which came to my mind was that I could create a XML inside the domain and transform this somehow. Can you give me any examples how to do so or even better solutions?

Upvotes: 1

Views: 447

Answers (1)

Andrew Orsich
Andrew Orsich

Reputation: 53705

I am sure that you can use comma separate string and than parse into table.

But i suggest to sent xml with ids like this:

<query id="59bd6806-d28c-451a-8473-69f1432e46b2">
  <ids>
    <id>1</id>
  </ids>
</query>

And than parse into table:

CREATE FUNCTION [dbo].[GetQueryIdsTable] 
(   
    @query xml
)
RETURNS @ids TABLE (id INT, ItemOrder INT)
AS
BEGIN

    INSERT INTO @ids (ID, ItemOrder) SELECT Ids.Id.value('.','int'), ROW_NUMBER() OVER(ORDER BY ROWCOUNT_BIG()) AS ItemOrder

    FROM @query.nodes('/query/ids/id') as Ids(Id)   

    RETURN 
END

After parsing you can join.

On the server side i use Xml builder like this:

public class Query
{
    private List<string> Ids = new List<string>();

    public void AddId(Int32 id)
    {
        if (Ids.Contains(id.ToString()))
            return;

        Ids.Add(id.ToString());
    }

    public String ToXmlString()
      {
        var idsElement = new XElement("ids");

        foreach (var id in Ids)
        {
            idsElement.Add(new XElement("id", id));
        }


        XElement query = new XElement("query",
                new XAttribute("id", Guid.NewGuid().ToString()),
            idsElement
         );

        return query.ToString();
       }

}

I like xml because you can what you want and easy parse at sql server.

Upvotes: 2

Related Questions