snippetsqlMinor
Is there a way to have SQL Server format a number into ordinal form?
Viewed 0 times
formatnumberordinalsqlintowayserverformtherehave
Problem
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.
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.
Solution
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
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
Context
StackExchange Database Administrators Q#6084, answer score: 5
Revisions (0)
No revisions yet.