Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question
1  
SQL Server does not provide these functions, but you can find some free UDF libraries that do. – Nick Chammas Sep 22 '11 at 19:50

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
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
This is just what we did, thank you. Here is the link I found and it worked great for part 2: sqlteam.com/forums/topic.asp?TOPIC_ID=86067 – Daniel Williams Sep 22 '11 at 21:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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