Parse delimited string into rows

Some time ago I had to build an integration app that would on occasion query from a SQL Server 2012 database and based on the returned data, call a REST API in another system.

One of the challenges, was that the SQL Query included an IN clause that contained a list of integer codes and this list of codes came from a configuration file. This list had to somehow be passed in to the SQL Statement as an argument. The SQL Statement was defined in the Integration Platform and was not to be stored in a Stored Procedure. Dynamically build SQL code also was not an option. In addition, this database did not have a STRING_SPLIT option as that wasn’t introduced until SQL Server 2016. And finally, changes to the database were not permitted, such as creating a customized string split function. Essentially the only option left was to pass in the string as an argument to the SQL Statement and parse it directly within the query itself.

In its most simplified form, the query looked something like this:

SELECT *
FROM SomeTable tbl
WHERE tbl.somevalue NOT IN (
  583,503,556,532,530,505,250,
  501,502,630,637,638,639
)

While not an ideal solution, the following approach worked and solved our problem:

SELECT *
FROM SomeTable tbl
WHERE tbl.somevalue NOT IN (
    SELECT Split.a.value('.', 'VARCHAR(100)')
    AS priority FROM (
      SELECT CAST (
        '<M>' +
        REPLACE(?, ',', '</M><M>') +
        '</M>' AS XML)
      AS Data
    ) AS a
    CROSS APPLY Data.nodes ('/M') AS Split(a)
  )

The list of codes was passed in as a comma separated string argument to the SQL Statement.

codes: ‘583,503,556,532,530,505,250,501,502,630,637,638,639’

Leave a Reply

Your email address will not be published. Required fields are marked *