|
Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to create and populate a sequence table, to create a user-defined function for title casing a sentence and test it:
USE AdventureWorks
GO
CREATE TABLE dbo.Sequence11 (
SeqNo INT PRIMARY KEY)
GO
INSERT Sequence11
SELECT TOP 1000 ROW_NUMBER()
OVER(ORDER BY c1.name)
FROM sys.columns c1
CROSS JOIN sys.columns c2
GO
-- SQL Server user-defined function - UDF - SQL scalar function
-- SQL title case formatting function
CREATE FUNCTION fnTitleCase
(@TextIn NVARCHAR(512))
RETURNS NVARCHAR(512)
AS
BEGIN
DECLARE @TitleCase NVARCHAR(256)
DECLARE @TextInGrid TABLE(
TextInput VARCHAR(256) NOT NULL
)
DECLARE @Delimiters TABLE(
Delimiter CHAR(1) NOT NULL
)
DECLARE @WordExclusion TABLE(
Modifiers VARCHAR(20) NOT NULL
)
INSERT @TextInGrid
VALUES(@TextIn)
INSERT @WordExclusion
(Modifiers)
SELECT ' the '
UNION
SELECT ' a '
UNION
SELECT ' an '
UNION
SELECT ' and '
UNION
SELECT ' with '
UNION
SELECT ' from '
UNION
SELECT ' off '
UNION
SELECT ' in '
UNION
SELECT ' on '
UNION
SELECT ' as '
UNION
SELECT ' into '
UNION
SELECT ' of '
UNION
SELECT ' to '
INSERT @Delimiters
(Delimiter)
SELECT ' '
UNION
SELECT '-'
UNION
SELECT ''''
SELECT @TitleCase = REPLACE((SELECT CASE
WHEN SeqNo = 1
OR (SUBSTRING(n.TextInput,SeqNo - 1,1) IN (SELECT Delimiter
FROM @Delimiters)
AND NOT EXISTS (SELECT *
FROM @WordExclusion e
WHERE Modifiers = SUBSTRING(n.TextInput,SeqNo - 1,LEN(Modifiers) + 1))) THEN UPPER(SUBSTRING(n.TextInput,SeqNo,1))
ELSE LOWER(SUBSTRING(n.TextInput,SeqNo,1))
END AS [text()]
FROM Sequence11
WHERE SeqNo <= LEN(n.TextInput)
ORDER BY SeqNo
FOR XML PATH( '' )),' ',' ')
FROM @TextInGrid n
RETURN @TitleCase
END
GO
SELECT TextInTitleCase = dbo.TitleCase('Latest Microsoft products driving hardware innovation and platform opportunity')
GO
/* Result
TextInTitleCase
Latest Microsoft Products Driving Hardware Innovation and Platform Opportunity
*/
-- Cleanup
DROP TABLE dbo.Sequence11
Related article:
SQL Server User-defined Functions
|
