Neuron ESB

Lately I've been learning how to use an ESB that my company (Neudesic) created. I'm really liking it, it seems very intuitive and easy to use. It has a bit of overlap with BizTalk functionality, but I think that both of them together make a great team. I'm not very fond of the BizTalk ESB Toolkit, so I'm glad to find something I like better.

The product is called Neuron ESB. I don't have time to go into detail at the moment (deadlines, you know!), but I plan to write more later. You can click on the link for more details.

SQL - Split Using Common Table Expression

In working with SQL server, I have often needed to use a Split function, which will take a string and a delimiter, and return a table of tokens from the string. The only problem is that my clients have not always been happy about needing to add a function to the DB.

I have recently discovered Common Table Expressions (CTEs), thanks to the San Diego Tech Immersion Group. I found a CTE that I was able to modify to produce the table of tokens from a string, just like the function above. Here's my modified query with a test string:

DECLARE @str AS NVARCHAR(MAX)
DECLARE @separator AS NVARCHAR(2)
  
SET @str = 'test1
test2
test3
test4'
SET @separator = CHAR(13) + CHAR(10)

;
WITH Split_CTE(rowNum, stringStart, stringEnd) AS
(
 SELECT
    1 AS rowNum,
    CAST(1 AS BIGINT) AS stringStart,
    CHARINDEX(@separator, @str) AS stringEnd
 UNION ALL
 SELECT
    rowNum + 1 AS rowNum,
    stringEnd + LEN(@separator) AS stringStart,
    CHARINDEX(@separator, @str, stringEnd + 1) AS stringEnd
 FROM Split_CTE
 WHERE stringEnd > 0
)
SELECT
 rowNum,
 SUBSTRING(@str, stringStart,
 CASE WHEN stringEnd > 0 THEN stringEnd - stringStart ELSE 2000000000 END) AS StringValue
FROM Split_CTE
;

I've tested this extensively for almost 2 minutes!

But seriously, if it fails when testing the application I'm working on now, I'll modify this post.