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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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)

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

sql-concatenation-in-sql-server






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems