6

Data 1, 2, 3, 4, 11, 21, 99

I want to get 1st, 2nd, 3rd, 11th, 21st, and 99th

I could roll my own, but is there any function built into SQL server that could so this for me? Corollary: I will also be looking for a function to turn numbers into words, as in:

72,451 -> seventy-two thousand, four hundred fifty-one

And would expect it (if it exists) to be in the same family of formatting methods.

5

No, there is not any built in function that will do this. This is something that should be handled in the presentation layer and not in the data coming from the database.

EDIT: based on the presence of a "crummy presentation layer", IF this would have to be done in the database I suggest creating a UDF to convert the values to ordinal or words. IT definitely would not be pretty.

From a comment: For the second part of the question, Daniel used the script found here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067

  • presentation layer is InfoMaker. very crummy program, so as much as we can push into the database, we do. – Daniel Williams Sep 22 '11 at 19:04
2

With regards to the second part of the request -- turning numbers into words -- there is a function, Util_ToWords available in the Free version of the SQL# SQLCLR library (which I am the author of, and again, this function is in the Free version) that does just this:

SELECT SQL#.Util_ToWords(72451);
-- Seventy Two Thousand, Four Hundred Fifty One

SELECT SQL#.Util_ToWords(72451.56);
-- Seventy Two Thousand, Four Hundred Fifty One and 56

The Util_ToWords function was originally intended to mimic a similar function available in Crystal Reports that helps with printing checks (in the US, at least).

Please note that the wording:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.