- 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 people,
All right with you ?
In this post today, I will share with you a Table-valued UDF function that lets you break strings into lines, forcing the maximum length of each line to be N characters separated by a separator character defined in the function call.
This function came from a need in a critical project where I work, where we have VARCHAR (MAX) strings and we need to export these strings into a TXT file with a maximum length of 60 characters, keeping an Id to identify the original record and a ranking (I used ROW_NUMBER) to identify the order of each part of the string.
Interested in learning more about split?
- Breaking strings into substrings using separator (Split string)
- Charindex: A different function to break split strings
- How to wrap a string in a substring table using a delimiter in SQL Server
Examples of use
Simple Example:
Example with CROSS APPLY:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Utilizando o caractere espaço (' ') como separador e tamanho máximo da string -- limitado a 10 caracteres IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Id_Texto INT IDENTITY(1, 1), Ds_Texto VARCHAR(MAX) ) INSERT INTO #Teste VALUES('Dirceu Resende - Testando a função do CLR numa table valued function'), ('No caso de uma palavra ficar maior que a quantidade de caracteres estipulada, a string não será "quebrada". Exemplo: https://dirceuresende.com'), ('Para saber mais sobre CLR, acesse o meu blog') SELECT * FROM #Teste A CROSS APPLY CLR.dbo.fncSplit_Texto(A.Ds_Texto, ' ', 10) B |
Result:
Function source code
To use the function shown above, simply create the table-valued CLR function in your instance. To better understand what the CLR is and how to create your first CLR library, see more in the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
using System; using System.Collections; using System.Data.SqlTypes; public partial class UserDefinedFunctions { private class SplitTexto { public SqlInt32 Id; public SqlString Ds_Palavra; public SplitTexto(SqlInt32 id, SqlString dsPalavra) { Id = id; Ds_Palavra = dsPalavra.IsNull ? "" : dsPalavra; } } [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillSplitTexto", TableDefinition = "Id INT, Ds_Palavra NVARCHAR(4000)" )] public static IEnumerable fncSplit_Texto(string Ds_Texto, string Ds_Separador, int Tamanho_Palavra) { var splitTextoCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Texto)) return splitTextoCollection; var contador = 1; var palavra = ""; Ds_Texto = Ds_Texto + Ds_Separador; while (Ds_Texto.Length > 0) { var substring = Ds_Texto.Substring(0, Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal)).Trim(); if (palavra == " ") palavra = ""; if ((palavra + " " + substring).Length > Tamanho_Palavra) { splitTextoCollection.Add(new SplitTexto( contador, palavra.Trim() )); palavra = substring; contador++; } else { palavra = palavra == " " ? substring : (palavra + " " + substring); } Ds_Texto = Ds_Texto.Substring(Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal) + 1); } if (palavra.Trim().Length > 0) { splitTextoCollection.Add(new SplitTexto( contador, palavra.Trim() )); } return splitTextoCollection; } protected static void FillSplitTexto(object objSplitTexto, out SqlInt32 id, out SqlString dsPalavra) { var splitTexto = (SplitTexto) objSplitTexto; id = splitTexto.Id; dsPalavra = splitTexto.Ds_Palavra.IsNull ? "" : splitTexto.Ds_Palavra; } }; |
That's it folks!
A hug and see you next.
