Converting numeric data type to text data type

[Gavin]

Hi,

I would like to convert a dollar amount ($1,500) to represent Fifteen
hundred dollars and 00/100 cents only for SQL reporting purposes. Is
this possible and can I incorporate the statement into an existing
left outer join query.

Thanks in advance,

Gavin

[Erland Sommarskog]

Re: Converting numeric data type to text data type

[posted and mailed, please reply in news]

Gavin (gsegal@mps.com ) writes:[color=blue]
> I would like to convert a dollar amount ($1,500) to represent Fifteen
> hundred dollars and 00/100 cents only for SQL reporting purposes. Is
> this possible and can I incorporate the statement into an existing
> left outer join query.[/color]

If it is for reporting issues, it may be better to do this on client
level, but you could use a table to hold the various strings. I would
suggest that it will be simpler to implement, if you permit 1500 to be
rendered as "One thousand five hundred".

The table would look like this:

CREATE TABLE numberstrs (nr tinyint NOT NULL
CONSTRAINT ck_nr CHECK (nr BETWEEN 0 AND 99),
str varchar(23) NOT NULL,
CONSTRAINT pk_nr PRIMARY KEY (nr))
go
INSERT numberstrs (nr, str) VALUES (0, '')
INSERT numberstrs (nr, str) VALUES (1, 'one')
...
INSERT numberstrs (nr, str) VALUES (99, 'ninety-nine')

You would then use it as

SELECT CASE WHEN mil.str IS NOT NULL
THEN mil.str + ' millions and '
ELSE ''
END +
CASE WHEN hth.str IS NOT NULL
THEN mil.str + ' hundred '
ELSE ''
END +
CASE WHEN th.str IS NOT NULL
THEN th.str + ' thousand and '
ELSE ''
END +
CASE WHEN hun.str IS NOT NULL
THEN hun.str + ' hundred '
ELSE ''
END +
one.str + ' dollars ' +
ltrim(str((a.am t * 100) % 100)) + '/100 cents'
FROM tbl a
JOIN numberstrs one ON a.amt % 100 = nr
LEFT JOIN numberstrs hun ON (convert(int, a.amt) / 100) % 10 = nr
LEFT JOIN numberstrs th ON (convert(int, a.amt) / 1000) % 100 = nr
LEFT JOIN numberstrs hth ON (convert(int, a.amt) / 100000) % 10 = nr
LEFT JOIN numberstrs mil ON (convert(int, a.amt) / 1000000) % 100 = nr

This is something I made up, and I have not tested it. I don't think
the result will be that excellent for all numbers. For instance 101
would not come out pretty if you want it as "One hundred and one".
For even values like "One million" you need to add some logic.

Notice that there is a upper limit of 100 millions as I have written the
query. You would also have to arrange for the first letter in the
resulting string to be uppercase.

You mention how would incorporate into an existing query, but since I
don't see that query and don't where you want the value, I'm not taking
a stab at that.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp