- SQL Server - Breaking strings into sub-strings using a separator (Split string)
- SQL Server - Table-valued function to break a string into lines up to N characters long
- How to wrap a string in a substring table using a delimiter in SQL Server
- SQL Server - Charindex: A Different Function to Break Split Strings
- SQL Server - Using STRING_SPLIT to transform strings from a row into columns
Hello guys!
Good night!
Today I will present a function created by my friend Murilo Mielke, which allows you to break a string delimited by some (or some) characters in substrings. For the web developer, that's what makes the explode function of PHP or Java Split, Javascript, C #, etc ..
Basically, you have a string like the example below:
nome;nascimento;email
Nome 1;1994-05-29;[email protected]
Nome 2;1981-07-10;[email protected]
Nome 3;2001-02-27;[email protected]
Imagine that you want to retrieve only the name and email from the records above. Dividing each line using the character “;” as a separator, we have 3 sub-strings. This is exactly what the function below does:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int ) RETURNS varchar(8000) AS BEGIN DECLARE @Index int, @Max int, @Retorno varchar(8000) DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) ) SET @Index = charIndex(@Separador,@String) WHILE (@Index > 0) BEGIN INSERT INTO @Partes SELECT SubString(@String,1,@Index-1) SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String)))) SET @Index = charIndex(@Separador,@String) END IF (@String != '') INSERT INTO @Partes SELECT @String SELECT @Max = Count(*) FROM @Partes IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5)) IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca RETURN RTRIM(LTRIM(@Retorno)) END GO |
Examples of use:
| 1 2 3 4 | DECLARE @strOrigem VARCHAR(MAX) = 'Testando|String|Para|O|Blog' SELECT dbo.fncSplit(@strOrigem, '|', 1) -- Vai imprimir na tela 'Testando' SELECT dbo.fncSplit(@strOrigem, '|', 5) -- Vai imprimir na tela 'Blog' |
Using the CLR
Another alternative to solve this problem is to use CLR, a feature that allows you to create .NET (C # or VB.NET) code written within SQL Server, where it generally performs much better than T-SQL code. If you don't know what SQLCLR is, learn more by accessing the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.
View C # Function (CLR) Source CodePerformance test
While the two functions bring us the same result, the way they are executed is totally different. The T-SQL function is simpler to implement (just an F5), and the CLR function takes more work to be created in the database, as it will be necessary to create an assembly in Visual Studio to use it (if you don't have any). If you already have one created, then it's easy) in addition to the fact that it is written in C #, a language widely known by developers, but not so much by DBA's (All of this was already mentioned in the SQL CLR introduction post).
Given these facts, you may be asking yourself, “So why use the CLR function? What is the advantage? ”. And the answer is this: PERFORMANCE.
Query used for testing:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT CLR.dbo.fncSplit(Descricao, '|', 1), CLR.dbo.fncSplit(Descricao, '|', 2), CLR.dbo.fncSplit(Descricao, '|', 3), CLR.dbo.fncSplit(Descricao, '|', 4) FROM dbo.Teste_Group_Concat SELECT dbo.fncSplit(Descricao, '|', 1), dbo.fncSplit(Descricao, '|', 2), dbo.fncSplit(Descricao, '|', 3), dbo.fncSplit(Descricao, '|', 4) FROM dbo.Teste_Group_Concat |
Result:
As you can see, the test table has 81.753 lines, where each line has 4 words separated by the character "|", and I applied the function 4 times, to retrieve each of those words, from each line.
The result presented a very convincing argument for using the CLR function: While the T-SQL function took 213,2 seconds to do this processing, the CLR function (which does the same thing) needed only 3,6 seconds. A performance difference of almost 60 times more if using the CLR.
Amazing!
sql server split explode split string strings table valued function
sql server split explode split string strings table valued function
That simple!
Until the next post.
Perfect this article and the Split function. I made some adjustments for the function to handle different types of data and also when null. Abs.
The function is very good… Congratulations!
I created the function but it is only bringing NULL as a result.
Would you help me?
Osmar, good afternoon and thanks for stopping by!
About your question .. What version of SQL Server are you using? How are you performing the function?
It broke a huge branch, Thanks!
Very useful, thanks!