Technical Article

Fetching Ordinal number text

,

Interested in getting the ordinal number as text?
You know, 1st, 32nd and so on?

Have a look at this script.

peso@developerworkshop.net

SELECT		Number,
		CASE
			WHEN Number % 100 IN (11, 12, 13) THEN 'th'
			WHEN Number % 10 = 1 THEN 'st'
			WHEN Number % 10 = 2 THEN 'nd'
			WHEN Number % 10 = 3 THEN 'rd'
			ELSE 'th'
		END Ordinal
FROM		master..spt_values
WHERE		Number BETWEEN 1 AND 255
		AND Name IS NULL
ORDER BY	Number

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,069 reads