sql-serversql-server-2005sql-server-2008pascalcasing

Convert text in PascalCase


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

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

The field that contains text to convert is CITTA. It contains all uppercase values like "ABANO TERME", "ROMA", and so on. The words are delimited by a space.

EDIT

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

EDIT 2:

Some quirks found on results:

  • If I have a name like "ISOLA BALBA" this name get translated to "IsolaBalba" (correct case but missed space)
  • If I have a name like "ISOLA D'ASTI" this get converted to "IsolaD'asti" (missed space as before and incorrect case. In this case the correct result is "Isola D'Asti"

could you please give me some advice on this small problem?


Solution

  • 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]