Freigeben über

TSQL - Solve it YOUR Way - Text to Speech

  • Artikel

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 https://blogs.lessthandot.com/index.php/All/?disp=authdir&author=218 and https://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.

Comments

  • Anonymous
    September 03, 2012
    Solve it your way is amazing!!! great initiative.

  • Anonymous
    September 11, 2012
    Nice techniques, but I can't help but wonder why this is being performed in the database. Not one of the replies in the original post posed this question to the original poster.

  • Anonymous
    September 12, 2012
    Please add your comments to the original post.

  • Anonymous
    September 12, 2012
    Hi Jon, thanks for the feedback.  I certainly agree that there is a valid argument for this processing/conversion to be handled by the application as opposed to the database layer.  The goal of our blog series is to take a forums question (or slight variation thereof) and look at the thought process behind different solutions in an effort to help people learn new techniques and to better understand the mindset of some of the most creative TSQL problem solvers in the forums.  When I saw this topic, I thought it would be a great candidate for the series based on the numerous ways to tackle the problem from a TSQL perspective.  Thanks again for your comment, it is a great point to keep in mind that often is left out of answers in the SQL Server forums.

  • Anonymous
    September 13, 2012
    Ken, the original post?

  • Anonymous
    September 13, 2012
    Tom's solution is the shortest. =^)