CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
Shashika
Shashika

Reputation: 219

How to sort varchar type column that contain both string and numeric values

I have a table that has column name "BatchNo" and it contain both string and numeric values together. when i typed a select query i got following results.

enter image description here

i want to sort "BatchNo" column in following format

enter image description here

In my problem future batches may be have a,b,c .... letters with numeric values or only numeric values. how can i sort this column ? Hope you guys can help me to get some solution.

Upvotes: 2

Views: 1479

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

SELECT *
FROM tableName
ORDER BY 
CAST(LEFT(BatchNo, PATINDEX('%[^0-9]%', BatchNo + 'Z')-1) AS INT),
BatchNo

SQL fiddle demo

Upvotes: 1

TechDo
TechDo

Reputation: 18639

How about this:

SELECT *
FROM TABLE_NAME
ORDER BY CAST(LEFT(BatchNo, PATINDEX('%[^0-9]%', BatchNo + 'A')-1) AS INT)

Query works for a string which starts with numeric values only.

Upvotes: 0

John Woo
John Woo

Reputation: 263733

Why don't you alter the schema of your table into?

CREATE TABLE tableName
(
    ShipmentNo INT,
    ProjectCode VARCHAR(50),
    BatchNo INT,
    SubBatch INT,
    .....
)

in this way, it is to sort the valkues the way you want.

follow-up question, is BatchNo has only single character value from A-Z, eg

11a
11b

and not 

11aa

UPDATE 1

SELECT   *
FROM     tableName
ORDER BY CASE ISNUMERIC(batchNo)
            WHEN 1 THEN batchNo
            ELSE CAST(LEFT(batchNo, LEN(batchNo) - 1) AS INT)
         END

SQLFiddle Demo

Upvotes: 2

Related Questions