SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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)
Code

sql-case-sensitive-split-string-function

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
Code

sql-concatenation-in-sql-server



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.