EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at firstname.lastname@example.org.
In a SQL Server 2000 database, I have a table of menu items containing the menuNumber and menuText text fields. The records in this table are in no particular order. How can I write a SELECT statement that returns the menu items in the order that Table 1 shows? I know that a sort on the menuNumber text field isn't sufficient because the sort would be character-based. For example, the sort would place 11 <something> lower than 2 <something>.
To write a query that returns the menu items in the order you want, you can create a user-defined function (UDF) that accepts a menu item, then prefixes each part in the item with zeros to make all parts the same length. When all parts are the same length, the character-based sort behaves the same as a numeric sort (e.g., the sort compares the ones digit with the other value's ones digit). The CREATE FUNCTION statement in Listing 1 creates the dbo.fn_stuffzeros() UDF. When you use this UDF in the SELECT statement
you produce the following output:
After creating and populating table T1 by running the code in Listing 2, you can achieve the desired results by using the UDF in an ORDER BY clause, as the following query shows:
SELECT * FROM T1 ORDER BY dbo.fn_stuffzeros(menuNumber)
Table 2 shows this query's output.
If performance isn't satisfactory when you use this UDF, you can add the UDF to the table as a computed column, then create a clustered index on that column. Because the clustered index arranges the table's data in the computed column's order and because the computations are precalculated, the table is in an optimal state for the specified sorting requirements. The following code adds the computed column to the table and creates a clustered index on it:
ALTER TABLE T1 ADD sort_col AS dbo.fn_stuffzeros(menuNumber) UNIQUE CLUSTERED
Because you added the clustered index on the computed column, the following query should perform well:
SELECT menuNumber, menuText FROM T1 ORDER BY sort_col
If you want to indent the menuText values to illustrate the menu item's nesting level, you can add spaces to match the number of periods in the menuNumber value, multiplied by a certain factor. To count the number of periods in the menuNumber value, use the T-SQL expression
LEN(menuNumber) - LEN(REPLACE(menuNumber, '.', ''))
The following query returns the proper indentation:
SELECT SPACE((LEN(menuNumber) - LEN(REPLACE(menuNumber, '.', '')) - 1) * 2) + menuText AS menuText FROM T1 ORDER BY sort_col