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

Answer 1

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 
*/

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

Tom, I went with your solution. I think it's simple and clean. Worked perfect for my project. Thanks!

---

~ rbrady5

Answer 10

Lots of great ways to achieve this. Thanks for all the feedback!

---

~ rbrady5

Answer 11

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.