Case Sensitive SQL Split Function
In this T-SQL tutorial, SQL developers can find a sample sql case sensitive split string function.
This sample SQL split string function identifies upper case letters and behaves as the start of a new word.
For example, let the input argument to the case sensitive sql split function be "ThisIsASqlServerCaseSensitiveSplitStringFunction".
We expect the output of the sql split function as identifying the words just like in the sentence : "This Is A Sql Server Case Sensitive Split String Function"
Here is the source code of the case sensitive sql split string function.
Please note that in order to identify capital letters, the SQL string function UPPER is used.
Each character in the string variable is compared with its UPPER case character using the case sensitive collation of the database.
If both values are equal, then t-sql developer can assume that a new word is being started.
Although the SQL Server database has the database collation as "Latin1_General_CI_AS" which is case insensitive, in our case sensitive sql split function we have to use the case sensitive version of that database collation.
And this is "Latin1_General_CS_AS".
And database collation is used in string compare conditions with the hint COLLATE.
So please pay attention to "COLLATE Latin1_General_CS_AS" within the below sql code.
In order to read each character in the sql string variable within split function, SubString sql string function is used.
CREATE FUNCTION CaseSensitiveSQLSplitFunction
(
@str nvarchar(max)
)
returns @t table (val nvarchar(max))
as
begin
declare @i int, @j int
select @i = 1, @j = len(@str)
declare @w nvarchar(max)
while @i <= @j
begin
if substring(@str,@i,1) = UPPER(substring(@str,@i,1)) collate Latin1_General_CS_AS
begin
if @w is not null
insert into @t (val) select @w
set @w = substring(@str,@i,1)
end
else
set @w = @w + substring(@str,@i,1)
set @i = @i + 1
end
if @w is not null
insert into @t (val) select @w
return
end
Here is a sample sql script where case sensitive sql split string function is used.
declare @str nvarchar(max) = N'ThisIsATest'
select * from dbo.CaseSensitiveSQLSplitFunction(@str)
set @str = N'ThisIsASqlServerCaseSensitiveSplitStringFunction'
select * from dbo.CaseSensitiveSQLSplitFunction(@str)
It is now possible to sql concatenate string values in a way from rows to single column value.
We can just use any of the sql concatenation function.
declare @str nvarchar(max) = N'ThisIsATest'
SELECT LTRIM(STUFF((
SELECT ' ' + val FROM dbo.CaseSensitiveSQLSplitFunction(@str) FOR XML PATH('')
), 1, 1, '')) string
set @str = N'ThisIsASqlServerCaseSensitiveSplitStringFunction'
SELECT LTRIM(STUFF((
SELECT ' ' + val FROM dbo.CaseSensitiveSQLSplitFunction(@str) FOR XML PATH('')
), 1, 1, '')) string
