Convert text to PascalCase

Question

Is it possible to convert text from a table column in SQL Server to PascalCase only using the appropriate SQL code?

TABLE DEFINITION ---------------------- ID int CITTA varchar(50) PROV varchar(50) CAP varchar(50) COD varchar(50) 

Field containing the text to convert, CITTA . It contains all uppercase values ​​such as "ABANO TERME", "ROMA", etc. Words are limited to space.

EDIT

I forgot to mention that some words have an accent in it. ' This symbol can be found either at the end of a word or in the middle.

EDIT 2:

Some quirks are found by results:

• If I have the name "ISOLA BALBA", that name translates to "IsolaBalba" (correct case, but missing space).
• If I have the name "ISOLA D'ASTI", this translates to "IsolaD'asti" (missing space, as before, and the wrong case). In this case, the correct result is Isola D'Asti

Could you give me some advice on this small issue?

Answer 1

 DECLARE @T TABLE ( ID INT PRIMARY KEY, CITTA VARCHAR(50) ) INSERT INTO @T SELECT 1, 'ABANO TERME' UNION ALL SELECT 2, 'ROMA' UNION ALL SELECT 3, 'ISOLA D''ASTI'; IF OBJECT_ID('tempdb..#HolderTable') IS NOT NULL DROP TABLE #HolderTable CREATE TABLE #HolderTable ( Idx INT IDENTITY(1,1) PRIMARY KEY, ID INT, Word VARCHAR(50) ) CREATE NONCLUSTERED INDEX ix ON #HolderTable(ID) ; WITH T1 AS ( SELECT ID, CAST(N'<root><r>' + REPLACE(REPLACE(CITTA, '''', '''</r><r>'), ' ', ' </r><r>') + '</r></root>' AS XML) AS xl FROM @T ) INSERT INTO #HolderTable SELECT ID, r.value('.','NVARCHAR(MAX)') AS Item FROM T1 CROSS APPLY xl.nodes('//root/r') AS RECORDS(r) SELECT ID, (SELECT STUFF(LOWER(Word),1,1,UPPER(LEFT(Word,1))) FROM #HolderTable WHERE [@T].ID = #HolderTable.ID ORDER BY Idx FOR XML PATH('') ) FROM @T [@T] 

Answer 2

I suggest you try the code that I posted on my blog a while ago. I suspect that it will satisfy your requirements very well, and will also work better than many other methods.

Correct work with SQL Server

 CREATE FUNCTION dbo.Proper(@DATA VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Position INT SELECT @DATA = STUFF(LOWER(@DATA), 1, 1, UPPER(LEFT(@DATA, 1))), @Position = PATINDEX('%[^a-zA-Z][az]%', @DATA COLLATE Latin1_General_Bin) WHILE @Position > 0 SELECT @DATA = STUFF(@DATA, @Position, 2, UPPER(SUBSTRING(@DATA, @Position, 2))), @Position = PATINDEX('%[^a-zA-Z][az]%', @DATA COLLATE Latin1_General_Bin) RETURN @DATA END 

This function is slightly faster than most, because for every word that requires a capital letter, it is executed only once.

Answer 3

Try the following function (adjust the line type if necessary). Just don't use this in a WHERE clause - and consider the performance difference elsewhere. 12345678 is just some kind of arbitrarily large value that you might want to replace with something more suitable!

 CREATE FUNCTION dbo.ufn_PascalCase(@str AS VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN SET @str = LOWER(@str) DECLARE @result VARCHAR(MAX) = '' DECLARE @spaceIndex INTEGER = CHARINDEX(' ', @str) WHILE @spaceIndex > 0 BEGIN SET @result += UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, @spaceIndex - 2) SET @str = SUBSTRING(@str, @spaceIndex + 1, 12345678) SET @spaceIndex = CHARINDEX(' ', @str) END SET @result += UPPER(SUBSTRING(@str, 1, 1)) + SUBSTRING(@str, 2, 12345678) RETURN @result END