0 How to Sort Alphanumeric Data in SQL

Introduction

If your SQL query is not returning the result-set in the order you are expecting, this article may be helpful to fix the issue.

Background

We all know that the ORDER BY keyword is used to sort a result-set by a specified column. It works great for most of the cases. But, for alphanumeric data, it may not return the result-set that you will be expecting. This article explains how this can be fixed easily.

Using the Code

Step 1

I have created a table named “Test” with two columns, as shown below:

The following data has been added to the “Test” table:

The “Order By” in the following SQL query may not return the result-set in the correct order.

 Collapse | Copy Code

Select ID From TestOrder by ID

Step 2

I have modified the ORDER BY clause as shown below, and it returned the results in the proper order.

(Note: The ID column is defined as varchar(20). So, I did the following to fix this issue:

• If ID is numeric, add 21 '0's in front of the ID value and get the last 20 characters
• If ID is not numeric, add 21 ‘’s at the end of the ID value and get the first 20 characters

 Collapse | Copy Code

Select ID 
From Test
ORDER BY
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
     When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
     Else ID
End

Step 3

I have changed the query to return the row numbers (used in pagination) and it worked!

(Note: ROW_NUMBER works only in SQL Server 2005 and above versions.)

 Collapse | Copy Code

Select Row_Number() Over (Order by
Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
                          When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
                        Else ID
               END) As RowNumber,
ID
From Test

Credits : codeproject.com

0 Concatenate row values T-SQL

DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME)
DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT)
DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT  *,
        ( 
                SELECT  u.FName + ','
                FROM    @Users u INNER JOIN     
                                @Reviewers rs ON u.UserID = rs.UserID
                WHERE   rs.ReviewID = r.ReviewID
                FOR XML PATH('')
        ) AS ProductsFROM    @Reviews r

0 COALESCE

Table : People

Name

Peter
Paul
Mary

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

Just some explanation (since this answer seems to get relatively regular views):

• Coalesce is really just a helpful cheat that accomplishes two things: 1) No need to initialize @Names with an empty string value. 2) No need to strip off an extra separator at the end.
• The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + NameFROM PeopleWHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

One method not yet shown via the XML data() command in MS SQL Server is:

Assume table called NameList with one column called FName,

select FName + ', ' as 'data()' from NameList for xml path('')