none
Parse number and convert to text

    Question

  • I need to get a Text-To-Speech system to read a number as individual digits. So, I need to convert a number into text. Like this:

    164285  - convert to - "One Six Four Two Eight Five"

    What is the best way to parse each digit and return as text?

    I have found several examples that convert the number to "One hundred sixty four thousand two hundred eighty five" But, this is not what I need.

    Thanks in advance for your time!

    - SQL Server 2005

    Monday, August 20, 2012 2:56 PM

Answers

  • Fun question, I imagine there are a lot of nice solutions to this, but here's a basic example using a case statement.  You could also use a lookup table instead to cut down the size.

    DECLARE @NumToCovert int
    DECLARE @i int
    DECLARE @NumStr varchar(100)
    DECLARE @Final varchar (1000) = ''
    
    SELECT @NumToCovert = 164285
    SELECT @NumStr = CAST(@NumToCovert as VARCHAR(100))  -- Number to convert
    
    WHILE LEN(@NumStr) > 0
    BEGIN
    
    	SELECT @i = SUBSTRING(@NumStr,1,1)  -- Grab the first digit
    
    	-- Case statement to return digit text (could also be in a lookup table)
    	SELECT @Final = @Final +
    		CASE @i
    			WHEN 1 THEN 'One '
    			WHEN 2 THEN 'Two '
    			WHEN 3 THEN 'Three '
    			WHEN 4 THEN 'Four '
    			WHEN 5 THEN 'Five '
    			WHEN 6 THEN 'Six '
    			WHEN 7 THEN 'Seven '
    			WHEN 8 THEN 'Eight '
    			WHEN 9 THEN 'Nine '
    			WHEN 0 THEN ' Zero '
    		END
    
    	-- Truncate the character we processed and repeat until no more digits
    	SELECT @NumStr = SUBSTRING(@NumStr,2,LEN(@NumStr))
    END
    
    SELECT @Final as FinalText


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, August 20, 2012 3:18 PM
    Moderator
  • One more way

    Declare @NbrInput int;
    Declare @WordOutput varchar(1000);
    Set @NbrInput = 164285;
    Set @WordOutput = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
     @NbrInput,0, 'Zero '), 1, 'One '), 2, 'Two '), 3, 'Three '), 4, 'Four '),
       5, 'Five '), 6, 'Six '), 7, 'Seven '), 8, 'Eight '), 9, 'Nine ');
    Select @WordOutput;

    Tom

    Monday, August 20, 2012 5:07 PM
  • Perhaps something like this:

    declare @test table(some_String varchar(10) );
    insert into @test
    select '164285' union all select '0987654321'
    ;
    select
      some_String,
        coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 1, 1),'') as int) + 1, 6)) + ' ','')
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 2, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 3, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 4, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 5, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 6, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 7, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 8, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 9, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 10, 1),'') as int) + 1, 6)) + ' ','') 
    from @test
    /* -------- Output: --------
    some_String 
    ----------- ----------------------------------------------------------------------
    164285      one six four two eight five 
    0987654321  zero nine eight seven six five four three two one 
    */

    Monday, August 20, 2012 3:08 PM
    Moderator
  • See this e.g.

    with r(m,c) as ( select cast('164285' as varchar) m , 0 c union all select cast(SUBSTRING('164285',c+1,1) as varchar),c+1 from r where c between 0 and 6 ), w as ( select 1 n, 'one' word union select 2, 'two' union select 3, 'three' union select 4, 'four' union select 5, 'five' union select 6, 'six' union select 7, 'seven' union select 8, 'eight' union select 9, 'nine'
    union
        select 0, 'zero'

    )
    SELECT DISTINCT  (SELECT STUFF((SELECT ' ' + word
     FROM (select 1 id, w.word from r inner join w on r.m=w.n
    where  len(m)=1)r1
     WHERE id  = t.id
     FOR XML PATH('')),1,1,'')) AS Cities
    FROM (select 1id, w.word from r inner join w on r.m=w.n
    where  len(m)=1) t


    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    Monday, August 20, 2012 3:59 PM
  • Try

    DECLARE @NumToText TABLE (
    	Number TINYINT
    	,Word VARCHAR(10)
    	)
    
    INSERT INTO @NumToText
    VALUES (
    	1
    	,'One'
    	)
    	,(
    	2
    	,'Two'
    	)
    	,(
    	3
    	,'Three'
    	)
    	,(
    	4
    	,'Four'
    	)
    	,(
    	5
    	,'Five'
    	)
    	,(
    	6
    	,'Six'
    	)
    	,(
    	7
    	,'Seven'
    	)
    	,(
    	8
    	,'Eight'
    	)
    	,(
    	9
    	,'Nine'
    	)
    	,(
    	0
    	,'Zero'
    	)
    
    DECLARE @Value VARCHAR(100) = '125790784533'
    	,@Output VARCHAR(max);
    
    WITH cte
    AS (
    	SELECT SUBSTRING(@Value, N.Number, 1) AS [NumberToParse]
    		,N.number
    	FROM master..spt_values N where N.type = 'P'
    		AND N.number BETWEEN 1
    			AND LEN(@Value)
    	)
    	,cte2
    AS (
    	SELECT cte.Number
    		,T.Word
    	FROM @NumToText T
    	INNER JOIN cte ON T.Number = cte.NumberToParse
    	)
    SELECT @Output = LTRIM((
    			SELECT ' ' + Word
    			FROM cte2
    			ORDER BY number
    			FOR XML PATH('')
    			))
    
    SELECT @Output AS Result


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, August 24, 2012 7:26 AM
    Moderator
  • rbrady, thanks for the great topic.  I ended up including it in the Solve it YOUR Way blog series here:

    http://blogs.msdn.com/b/samlester/archive/2012/08/28/tsql-solve-it-your-way-text-to-speech.aspx

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, October 17, 2012 5:09 PM
    Moderator

All replies

  • Perhaps something like this:

    declare @test table(some_String varchar(10) );
    insert into @test
    select '164285' union all select '0987654321'
    ;
    select
      some_String,
        coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 1, 1),'') as int) + 1, 6)) + ' ','')
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 2, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 3, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 4, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 5, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 6, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 7, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 8, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 9, 1),'') as int) + 1, 6)) + ' ','') 
      + coalesce(rtrim(substring('zero  one   two   three four  five  six   seven eight nine ', 
          6*cast(nullif(substring(some_String, 10, 1),'') as int) + 1, 6)) + ' ','') 
    from @test
    /* -------- Output: --------
    some_String 
    ----------- ----------------------------------------------------------------------
    164285      one six four two eight five 
    0987654321  zero nine eight seven six five four three two one 
    */

    Monday, August 20, 2012 3:08 PM
    Moderator
  • Fun question, I imagine there are a lot of nice solutions to this, but here's a basic example using a case statement.  You could also use a lookup table instead to cut down the size.

    DECLARE @NumToCovert int
    DECLARE @i int
    DECLARE @NumStr varchar(100)
    DECLARE @Final varchar (1000) = ''
    
    SELECT @NumToCovert = 164285
    SELECT @NumStr = CAST(@NumToCovert as VARCHAR(100))  -- Number to convert
    
    WHILE LEN(@NumStr) > 0
    BEGIN
    
    	SELECT @i = SUBSTRING(@NumStr,1,1)  -- Grab the first digit
    
    	-- Case statement to return digit text (could also be in a lookup table)
    	SELECT @Final = @Final +
    		CASE @i
    			WHEN 1 THEN 'One '
    			WHEN 2 THEN 'Two '
    			WHEN 3 THEN 'Three '
    			WHEN 4 THEN 'Four '
    			WHEN 5 THEN 'Five '
    			WHEN 6 THEN 'Six '
    			WHEN 7 THEN 'Seven '
    			WHEN 8 THEN 'Eight '
    			WHEN 9 THEN 'Nine '
    			WHEN 0 THEN ' Zero '
    		END
    
    	-- Truncate the character we processed and repeat until no more digits
    	SELECT @NumStr = SUBSTRING(@NumStr,2,LEN(@NumStr))
    END
    
    SELECT @Final as FinalText


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, August 20, 2012 3:18 PM
    Moderator
  • See this e.g.

    with r(m,c) as ( select cast('164285' as varchar) m , 0 c union all select cast(SUBSTRING('164285',c+1,1) as varchar),c+1 from r where c between 0 and 6 ), w as ( select 1 n, 'one' word union select 2, 'two' union select 3, 'three' union select 4, 'four' union select 5, 'five' union select 6, 'six' union select 7, 'seven' union select 8, 'eight' union select 9, 'nine'
    union
        select 0, 'zero'

    )
    SELECT DISTINCT  (SELECT STUFF((SELECT ' ' + word
     FROM (select 1 id, w.word from r inner join w on r.m=w.n
    where  len(m)=1)r1
     WHERE id  = t.id
     FOR XML PATH('')),1,1,'')) AS Cities
    FROM (select 1id, w.word from r inner join w on r.m=w.n
    where  len(m)=1) t


    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    Monday, August 20, 2012 3:59 PM
  • Fun question, I imagine there are a lot of nice solutions to this, but here's a basic example using a case statement.  You could also use a lookup table instead to cut down the size.

    DECLARE @NumToCovert int
    DECLARE @i int
    DECLARE @NumStr varchar(100)
    DECLARE @Final varchar (1000) = ''
    
    SELECT @NumToCovert = 164285
    SELECT @NumStr = CAST(@NumToCovert as VARCHAR(100))  -- Number to convert
    
    WHILE LEN(@NumStr) > 0
    BEGIN
    
    	SELECT @i = SUBSTRING(@NumStr,1,1)  -- Grab the first digit
    
    	-- Case statement to return digit text (could also be in a lookup table)
    	SELECT @Final = @Final +
    		CASE @i
    			WHEN 1 THEN 'One '
    			WHEN 2 THEN 'Two '
    			WHEN 3 THEN 'Three '
    			WHEN 4 THEN 'Four '
    			WHEN 5 THEN 'Five '
    			WHEN 6 THEN 'Six '
    			WHEN 7 THEN 'Seven '
    			WHEN 8 THEN 'Eight '
    			WHEN 9 THEN 'Nine '
    			WHEN 0 THEN ' Zero '
    		END
    
    	-- Truncate the character we processed and repeat until no more digits
    	SELECT @NumStr = SUBSTRING(@NumStr,2,LEN(@NumStr))
    END
    
    SELECT @Final as FinalText


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Get error: "Cannot assign a default value to a local variable." when parsing query. I think this is due to fact that I am on SQL Server 2005 and I don't think you can assign default value on 2005 sql server.

    When I remove this default assignment error goes away. But, query returns NULL for FinalText



    ~ rbrady5

    Monday, August 20, 2012 4:35 PM
  • Ah, yes, forgot about that.

    Split (DECLARE @Final varchar (1000) = '') into two lines.

    DECLARE @Final varchar (1000) 
    SELECT @Final = ''

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, August 20, 2012 5:02 PM
    Moderator
  • One more way

    Declare @NbrInput int;
    Declare @WordOutput varchar(1000);
    Set @NbrInput = 164285;
    Set @WordOutput = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
     @NbrInput,0, 'Zero '), 1, 'One '), 2, 'Two '), 3, 'Three '), 4, 'Four '),
       5, 'Five '), 6, 'Six '), 7, 'Seven '), 8, 'Eight '), 9, 'Nine ');
    Select @WordOutput;

    Tom

    Monday, August 20, 2012 5:07 PM
  • Great solutions!

    Thanks,
    Sam Lester - MSFT


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Wednesday, August 22, 2012 5:46 PM
    Moderator
  • Try

    DECLARE @NumToText TABLE (
    	Number TINYINT
    	,Word VARCHAR(10)
    	)
    
    INSERT INTO @NumToText
    VALUES (
    	1
    	,'One'
    	)
    	,(
    	2
    	,'Two'
    	)
    	,(
    	3
    	,'Three'
    	)
    	,(
    	4
    	,'Four'
    	)
    	,(
    	5
    	,'Five'
    	)
    	,(
    	6
    	,'Six'
    	)
    	,(
    	7
    	,'Seven'
    	)
    	,(
    	8
    	,'Eight'
    	)
    	,(
    	9
    	,'Nine'
    	)
    	,(
    	0
    	,'Zero'
    	)
    
    DECLARE @Value VARCHAR(100) = '125790784533'
    	,@Output VARCHAR(max);
    
    WITH cte
    AS (
    	SELECT SUBSTRING(@Value, N.Number, 1) AS [NumberToParse]
    		,N.number
    	FROM master..spt_values N where N.type = 'P'
    		AND N.number BETWEEN 1
    			AND LEN(@Value)
    	)
    	,cte2
    AS (
    	SELECT cte.Number
    		,T.Word
    	FROM @NumToText T
    	INNER JOIN cte ON T.Number = cte.NumberToParse
    	)
    SELECT @Output = LTRIM((
    			SELECT ' ' + Word
    			FROM cte2
    			ORDER BY number
    			FOR XML PATH('')
    			))
    
    SELECT @Output AS Result


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, August 24, 2012 7:26 AM
    Moderator
  • Tom, I went with your solution. I think it's simple and clean. Worked perfect for my project. Thanks!

    ~ rbrady5

    Monday, August 27, 2012 1:19 PM
  • Lots of great ways to achieve this. Thanks for all the feedback!

    ~ rbrady5

    Monday, August 27, 2012 1:20 PM
  • rbrady, thanks for the great topic.  I ended up including it in the Solve it YOUR Way blog series here:

    http://blogs.msdn.com/b/samlester/archive/2012/08/28/tsql-solve-it-your-way-text-to-speech.aspx

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, October 17, 2012 5:09 PM
    Moderator