# 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.

- SQL Server 2005

Monday, August 20, 2012 2:56 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.

Monday, August 20, 2012 3:18 PM
• 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
• 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.nwhere  len(m)=1)r1 WHERE id  = t.id FOR XML PATH('')),1,1,'')) AS CitiesFROM (select 1id, w.word from r inner join w on r.m=w.nwhere  len(m)=1) t```

vt

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
• 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

### 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
• 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
• 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.nwhere  len(m)=1)r1 WHERE id  = t.id FOR XML PATH('')),1,1,'')) AS CitiesFROM (select 1id, w.word from r inner join w on r.m=w.nwhere  len(m)=1) t```

vt

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

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
• 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
• 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
• Tom, I went with your solution. I think it's simple and clean. Worked perfect for my project. Thanks!

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

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