|
Execute the following
script in 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 10000 ROW_NUMBER()
OVER (ORDER BY c1.name, c2.name)
FROM sys.columns c1 CROSS JOIN sys.columns c2
GO
CREATE FUNCTION TitleCase ( @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
|
