Introduction:

As part of the blog series TSQL - Solve it YOUR Way, I will present a topic recently discussed in the Transact-SQL MSDN forum here, followed by three different solutions to this problem supplied by three of the most active and helpful contributors in the forums, Tom Cooper, Naomi Nosonovsky, and Kent Waldrop.  More importantly, they have included thoughts as to how they arrived at each solution.  By seeing the different solutions along with a glimpse into their thought process, you can compare the different approaches to the solutions and learn from each of them.

Topic:  I am writing a Text to Speech application and want to parse integer values into digits and convert them into the text representation of each digit.  Example:  Convert 16498 into the string "one six four nine eight".
 

Solution #1:  Provided by Tom Cooper

 

Code Snippet
  1. -- Test Data
  2. Declare @Test Table(InputString varchar(10));
  3. Insert @Test(InputString) Values ('164285'), ('1746');
  4.  
  5. -- Solution
  6. SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
  7. InputString, '0', 'Zero '), '1', 'One '), '2', 'Two ')
  8. , '3', 'Three '), '4', 'Four '), '5', 'Five ')
  9. , '6', 'Six '), '7', 'Seven '), '8', 'Eight ')
  10. , '9', 'Nine ')
  11. FROM @Test;


Tom's Explanation: 

The reason that I picked this solution is it is simple and easy to understand.  That means it is easy to maintain. Maintainability is a very important consideration when developing software.  So, for example, let's consider sometime in the future that this problem is extended to include the requirement that the number may have embedded commas, and/or the number may be preceded by a minus sign (-).  If the requirement is to remove the comma’s and change the minus sign to the word  “Minus”, then you could give this to a junior programmer who could easily understand this code and make the updates.  Note, I have changed this slightly from my original answer.  I have made the second argument to each REPLACE function call a string constant, not an integer constant.  Either way works, but, of course, since REPLACE wants a string there, it is better to give it as a string rather than relying on the implicit conversion that will take place.


 

Solution #2:  Provided by Naomi Nosonsovsky

 

Code Snippet
  1. DECLARE @NumToText TABLE (
  2.         Number TINYINT
  3.         ,Word VARCHAR(10))
  4.  
  5. -- Create lookup table
  6. INSERT INTO @NumToText
  7. VALUES
  8. (1,'One'),
  9. (2,'Two'),
  10. (3,'Three'),
  11. (4,'Four'),
  12. (5,'Five'),
  13. (6,'Six'),
  14. (7,'Seven'),
  15. (8,'Eight'),
  16. (9,'Nine'),
  17. (0,'Zero')
  18.  
  19. -- Test values
  20. DECLARE @Value VARCHAR(100) = '125790784533'
  21.        ,@Output VARCHAR(max);
  22.  
  23. WITH cte
  24. AS (
  25.               SELECT SUBSTRING(@Value, N.Number, 1) AS [NumberToParse]
  26.                      ,N.number
  27.               FROM master..spt_values N where N.type = 'P'
  28.                                         AND N.number BETWEEN 1
  29.                                         AND LEN(@Value)
  30.               )
  31.               ,cte2
  32. AS (
  33.               SELECT cte.Number    
  34.                     ,T.Word
  35.               FROM @NumToText T
  36.               INNER JOIN cte ON T.Number = cte.NumberToParse
  37.               )
  38. SELECT @Output = LTRIM((
  39.                             SELECT ' ' + Word
  40.                             FROM cte2
  41.                             ORDER BY number
  42.                             FOR XML PATH('')
  43.                        ))
  45. SELECT @Output AS Result


Naomi's Explanation: 

I first created a table that contains the mapping/conversion (ex:  1:one, 2:two, etc).  Then using the numbers table, I parse the original value into individual digits using common table expressions (CTE's), join that with the conversion/lookup table, and then finally concatenate back using the XML PATH approach in the final @Output parameter.  

 

Solution #3 - Provided by Kent Waldrop 

 

Code Snippet
  1. declare @test table(some_String varchar(10) );
  2. insert into @test
  3. select '164285' union all select '0987654321'
  4. ;
  5. /* ------------------------------------------------
  6. This query gets the name of a string of digits.   
  7. (1) "name_List" provides a list of digit names as a string that is divided into 6-char segments -- a six-char segment
  8. that names each decimal digit.    
  9. (2) "digits" fetches each individual digit of the input string.  
  10. (3) "set_Names" fetches the name of each specific digit
  11. (4) the SELECT clause concatenates together the names obtained from "set_Names" to form the output string.
  12. ------------------------------------------------ */
  13. select
  14. some_String,
  15.       coalesce(seg_1, '')
  16.     + coalesce(seg_2, '')
  17.     + coalesce(seg_3, '')
  18.     + coalesce(seg_4, '')
  19.     + coalesce(seg_5, '')
  20.     + coalesce(seg_6, '')
  21.     + coalesce(seg_7, '')
  22.     + coalesce(seg_8, '')
  23.     + coalesce(seg_9, '')
  24.     + coalesce(seg_10, '')
  25. from @test
  26. cross apply
  27.     ( select'zero one two three four five six seven eight nine '      
  28.         as segments
  29. ) as name_List
  30. cross apply
  31. ( select
  32.         nullif(substring(some_String, 1, 1),'') as digit_1,
  33.         nullif(substring(some_String, 2, 1),'') as digit_2,
  34.         nullif(substring(some_String, 3, 1),'') as digit_3,
  35.         nullif(substring(some_String, 4, 1),'') as digit_4,
  36.         nullif(substring(some_String, 5, 1),'') as digit_5,
  37.         nullif(substring(some_String, 6, 1),'') as digit_6,
  38.         nullif(substring(some_String, 7, 1),'') as digit_7,
  39.         nullif(substring(some_String, 8, 1),'') as digit_8,
  40.         nullif(substring(some_String, 9, 1),'') as digit_9,
  41.         nullif(substring(some_String,10, 1),'') as digit_10
  42. ) as digits
  43. cross apply
  44. ( select
  45.         rtrim(substring(segments, 6*cast(digit_1 as int) + 1, 6)) + ' ' as seg_1,
  46.         rtrim(substring(segments, 6*cast(digit_2 as int) + 1, 6)) + ' ' as seg_2,
  47.         rtrim(substring(segments, 6*cast(digit_3 as int) + 1, 6)) + ' ' as seg_3,
  48.         rtrim(substring(segments, 6*cast(digit_4 as int) + 1, 6)) + ' ' as seg_4,
  49.         rtrim(substring(segments, 6*cast(digit_5 as int) + 1, 6)) + ' ' as seg_5,
  50.         rtrim(substring(segments, 6*cast(digit_6 as int) + 1, 6)) + ' ' as seg_6,
  51.         rtrim(substring(segments, 6*cast(digit_7 as int) + 1, 6)) + ' ' as seg_7,
  52.         rtrim(substring(segments, 6*cast(digit_8 as int) + 1, 6)) + ' ' as seg_8,
  53.         rtrim(substring(segments, 6*cast(digit_9 as int) + 1, 6)) + ' ' as seg_9,
  54.         rtrim(substring(segments, 6*cast(digit_10 as int) + 1, 6)) + ' ' as seg_10
  55. ) as set_Names;

 

Kent's Explanation:   

I used the APPLY operators because it is a method of clarifying the code with which I am comfortable.  Frequently, I will initially build a query in-line and then push complexity down into APPLY operators.  This is a design alternative to use of CTEs or derived tables.  However, if I am working with either Oracle or DB2 I tend toward the CTEs because the APPLY operator does not exist for either of these dialects.  Notice how well the CROSS APPLY partitions the calculations into more easily understandable portions.  Of course the same thing could be done with CTEs or derived tables.

There are a number of bookend operations that can be used for conditional processing. The most widely used combination is the CASE structure.  Other potentials bookends include the use of NULLIF and COALESCE, the SIGN function and the IIF function if you using SQL Server 2012.  In this case I chose to use the NULLIF and COALESCE bookends for conditional processing – but why?

Look again at the structure of the code.  Notice that the COALESCE function is in the SELECT clause and that the NULLIF is in one of the CROSS APPLY clauses.  By using these two operations to bookend my conditional code I can divide my code across a big boundary like this in order to reveal each distinct portion of the code.

 

Conclusion: 

 

As you can see, all three of the above solutions provide the result we were looking for, but do so in a very different style.  The original thread provides variations of the solutions presented here as well as one additional solution using a CASE statement.  Each of these solutions leverages different SQL Server language constructs and includes different considerations in the final solutions. I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.


Special thanks to Tom, Naomi, and Kent for their valuable forums contribution and for contributing to this series!


Hope that helps,
Sam Lester (MSFT)

 

Contributor Bios:

Tom Cooper began his programming career in 1968, began working with database software in 1977, and first worked with Microsoft SQL Server in 1994 (version 4.21).  He is now very happily retired.

Naomi Nosonovsky, Senior Software Developer, has more than 15 years of enterprise experience in analysis, design, development and implementation of high-performance client/server data management solutions. She is a Personality of the Year for 2008, 2009, 2010 and 2011 at www.universalthread.com in .NET, SQL Server & VFP categories. She is also an All Star contributor/MCC Community Contributor at forums.asp.net and MSDN T-SQL forum.  She also actively blogs at http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=218 and http://beyondrelational.com/members/naomi/modules/2/posts.aspx?Tab=16.

Kent Waldrop started working with Sybase Transact SQL in 1989 as an application developer and continued working with Sybase until 1995 when SQL Server 6 came out.  At that time, he became a Microsoft SQL Server database administrator and has continued to work with Microsoft SQL Server ever since.  Currently he is a database architect working with Microsoft SQL Server, Oracle and UDB/DB2.