[Challenge] 建立 SQL Server 版本的 Oracle INITCAP() 函數

Beyond Relational TSQL Challenge 22

This challenge deals with converting the phrases to proper case.In Oracle/PLSQL, the INITCAP function sets the first character in each word to uppercase and the rest to lowercase.For example, initcap('challenge'); would return 'Challenge'.The challenge to is implement similarly functionality in TSQL, that works similar to INITCAP function of ORACLE.

• Sample Data

ID Data
-- -----------------------------
1  welcome to TSQL Challenge #22

• Expected Results

ID Data                 Results
-- ----------------------------- ----------------------------- 
1  welcome to TSQL Challenge #22 Welcome To TSQL Challenge #22

• Rules

1. Results should be sorted in ascending order of ID.
2. The program should run in SQL SERVER 2005 and above.
3. The output should be in the same way as it has been shown.

• 個人解法

DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(100))
INSERT INTO @t 
SELECT 'welcome to TSQL Challenge #22'

;
WITH CTE AS 
(
  SELECT 
    ID , 
    Data , 
    1 AS Start , 
    CHARINDEX(SPACE(1),Data) AS [End]
  FROM @t
  UNION ALL
  SELECT 
    ID , 
    Data , 
    [End] + 1 , 
    CHARINDEX(SPACE(1),Data,[End] +1)
  FROM CTE
  WHERE [End] > 0
)
,
CTE2 AS
(
  SELECT 
    T.ID ,
    T.Data ,
    T.Start ,
    UPPER(LEFT(String , 1)) +
      LOWER(RIGHT(String , LEN(String) - 1)) AS String
  FROM
    (
      SELECT 
        ID , 
        Data ,
        Start ,
        SUBSTRING(Data , Start , ISNULL(NULLIF([End],0),LEN([Data]) + 1) - Start) AS String
      FROM CTE
    ) AS T
)
SELECT 
  T1.ID , 
  T1.Data ,
  STUFF
    (
      (
        SELECT SPACE(1) + T2.String
        FROM CTE2 AS T2
        WHERE T1.ID = T2.ID AND T1.Data = T2.Data
        ORDER BY Start
        FOR XML Path('')
      )
      ,1
      ,1
      ,''
    ) AS Results
FROM CTE2 AS T1
GROUP BY  T1.ID , T1.Data
ORDER BY T1.ID

利用 CTE 把每個字串給拆開，再使用 LEFT() 來大寫第一個字母和 RIGHT() 來抓取剩餘的文字，最後老方法 FOR XML PATH 把字串再組起來 ~~

參考資料
• TSQL Beginners Challenge 22 - Create SQL Server version of the ORACLE INITCAP function