[Challenge] 從數字字串中把數字和字母分開

Beyond Relational TSQL Beginners Challenge 10

This challenge refers to sanitizing text data. Your task is to read an alphanumeric string and create two output columns one containing all the digits found in the input string and the other with all the letters of the English Alphabet.

• Sample Data

Id AlphanumericData
-- ---------------------------------------------------------------------
1  I would like to invite my readers to participate in a TSQL Challenge.
2  You will receive 12 records
3  1234567

• Expected Results

Id AlphanumericData            Numbers    Alphabets
-- --------------------------- ---------  -----------------------------
1  I would like to invite my   No Number  Iwouldliketoinvitemyreaders
   readers to participate in   Found      toparticipateinaTSQLChallenge   
   a TSQL Challenge.         
2  You will receive 12 records 12         Youwillreceiverecords
3  1234567                     1234567    No Alphabets Found

• Rules
1. The program should run in SQL SERVER 2005+.
2. Column names should respect the desired output shown.
3. Output must be sorted in Ascending order of Id.
4. Ignore those records where there is no data.
5. If the alphanumeric data does not contain any numeric record, then the Number field should display "No Number Found".
6. If the alphanumeric data does not contain any alphabetic record, then the Alphabets field should display "No Alphabets Found".

• 個人解法

DECLARE @tblAlphanumeric TABLE(Id INT IDENTITY,AlphaNumericData VARCHAR(1000))
INSERT INTO @tblAlphanumeric (AlphaNumericData)
SELECT 'I would like to invite my readers to participate in a TSQL Challenge.' 
UNION ALL
SELECT 'You will receive 12 records' 
UNION ALL
SELECT '1234567' 

;
WITH CTE AS
(
  SELECT ID , AlphaNumericData , 1 AS Start , CHARINDEX(SPACE(1),AlphaNumericData,1) AS [End]
  FROM @tblAlphanumeric
  UNION ALL
  SELECT ID , AlphaNumericData , [End] + 1 ,  CHARINDEX(SPACE(1),AlphaNumericData,[End] + 1)
  FROM cTE
  WHERE [END] > 0
)
SELECT
  A.ID , 
  A.AlphaNumericData, 
  ISNULL(T1.String , 'No Number Found') AS Number , 
  CASE 
    WHEN T1.String IS NULL THEN REPLACE(A.AlphaNumericData,SPACE(1),'')
    WHEN A.AlphaNumericData = T1.String THEN 'No Alphabets Found'
    ELSE REPLACE(REPLACE(A.AlphaNumericData,T1.String,''),SPACE(1),'')
  END AS Alphabets
FROM @tblAlphanumeric AS A
  LEFT JOIN
    (
      SELECT T.*
      FROM
        (
          SELECT
            ID , 
            AlphaNumericData ,
            SUBSTRING(AlphaNumericData,Start,ISNULL(NULLIF([End],0),LEN(AlphaNumericData) + 1) - Start) AS String
          FROM CTE
        ) AS T
      WHERE T.String LIKE '[0-9]%'
    ) AS T1 ON A.ID = T1.ID
ORDER BY A.ID

參考資料
• TSQL Beginners Challenge 10 - Clean up alphanumeric strings by splitting digits and letters