how i can take order by while using nvarchar column in sql

Question

I have a query like this:

SELECT [Drug_id]
FROM tbl_Receiving
ORDER BY CAST(SUBSTRING([Drug_id], 3, LEN([Drug_ID])) as int)

but result is getting

A-1
A-10A
A-11
A-2
A-20A
A-21
A-22
A-3
A-30
A-31

But I need to get in all proper ascending order.

   A-1
   A-2
   A-3
   A-10
   A-11

My [Drug_id] column is nvarchar(50)

which database you are using? – mkRabbani Jun 20 '19 at 05:42 — mkRabbani, Jun 20 '19 at 05:42
Where do the values that end in "A" fit in? – Gordon Linoff Jun 20 '19 at 11:29 — Gordon Linoff, Jun 20 '19 at 11:29

score 0 · Answer 1 · answered Jun 20 '19 at 05:52

0

Assuming that your format is always A-<NUMBER>, you could always do the following:

SELECT 
    [Drug_id]
FROM 
    tbl_Receiving
ORDER BY 
    CAST(SUBSTRING([Drug_id], 3, LEN([Drug_ID])) as int)

This will then only try cast from the number onwards as an int

Output will be the following:

answered Jun 20 '19 at 05:52

Matt

2,851
1
13
27

Conversion failed when converting the nvarchar value '15A' to data type int. – user3262364 Jun 20 '19 at 05:54
What type of characters are in these strings? Are they all just alpha-numeric strings? (Alphabet characters & Numbers) – Matt Jun 20 '19 at 06:05
Alphabet characters & Numbers – user3262364 Jun 20 '19 at 06:08
this type of string P-22A,T-6A – user3262364 Jun 20 '19 at 06:09

score 0 · Answer 2 · answered Jun 20 '19 at 05:52

0

If the string formatting is defined as you shown in your sample data, You can use SUBSTRING and CHARINDEX to split the string before (-) and the INT value after (-). Then you can apply Ordering on them to get your desired data.

Note: Ofcourse there are performance issue with those String operations.

SELECT *
FROM your_tabele
ORDER BY SUBSTRING(val,1,CHARINDEX('-',Val,1)-1),
CAST(SUBSTRING(val,CHARINDEX('-',Val,1)+1,LEN(val)-CHARINDEX('-',Val,1)) AS INT)

answered Jun 20 '19 at 05:52

mkRabbani

16,295
2
15
24

Conversion failed when converting the nvarchar value '15A' to data type int. – user3262364 Jun 20 '19 at 05:55
As I mentioned, the formatting must need to be -. its showing error as there are (-) not available in the string 15A. Also it will work only if the string is first not Integer as in 15A. – mkRabbani Jun 20 '19 at 05:59
so in my case what i can do? – user3262364 Jun 20 '19 at 06:01
Please show the variety of combination in data in the sample data in your question. Then we can think if any logic can be applied or not. You only given one format which is misguiding us to think about the logic. – mkRabbani Jun 20 '19 at 06:02
Most importantly, you can not apply logic if the input is in random format. – mkRabbani Jun 20 '19 at 06:05
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/195244/discussion-between-mkrabbani-and-user3262364). – mkRabbani Jun 20 '19 at 06:06

Red Devil · Answer 3 · 2019-06-20T06:14:43.153

0

Try this: Run this query

    select substring(drug_id,1,CHARINDEX('-',drug_id)) + convert(varchar,cast(substring(drug_id,CHARINDEX('-',drug_id)+1,len(drug_id)) as int)) id from tbl_Receiving
order by  cast(substring(drug_id,CHARINDEX('-',drug_id)+1,len(drug_id)) as int)

edited Jun 20 '19 at 06:14

answered Jun 20 '19 at 06:02

Red Devil

2,343
2
21
41

replace @t with your table name. I have used only 4 records for testing – Red Devil Jun 20 '19 at 06:06
need to replace all @ t with table name – user3262364 Jun 20 '19 at 06:09
select substring(drug_id,1,CHARINDEX('-',drug_id)) + convert(varchar,cast(substring(drug_id,CHARINDEX('-',drug_id)+1,len(drug_id)) as int)) id from @t order by cast(substring(drug_id,CHARINDEX('-',drug_id)+1,len(drug_id)) as int) -- here replace @t with table name – Red Devil Jun 20 '19 at 06:11
Conversion failed when converting the nvarchar value '15A' to data type int. – user3262364 Jun 20 '19 at 06:31

Tom H · Answer 4 · 2019-06-20T07:25:03.860

I've basically copied this from this answer: https://stackoverflow.com/a/16667778/9101689

It uses patindex to identify the position of the numeric values and substrings out the actual value. We take that substring and the use the LEFT function to trim everything out to the right and cast that value as an int to order.

declare @d table (drug_id nvarchar(max))
insert into @d values ('A-1'),('A-10A'),('A-11'),('A-2'),('A-20A'),('A-21'),('A-22'),('A-3'),('A-30'),('A-31')
SELECT *
FROM (
    SELECT ORD_subsrt = SUBSTRING(drug_id, ORD_pos, LEN(drug_id)), *
    FROM (
        SELECT ORD_pos = PATINDEX('%[0-9]%', drug_id), *
        FROM @d
    ) d
) t
Order by cast(LEFT(ORD_subsrt, PATINDEX('%[^0-9]%', ORD_subsrt + 't') - 1) as int)

The value t is added so the patindex '%[^0-9]%' will return a non zero value in the case there is no non numeric suffix on drug_id field.

Or less readable:

SELECT *
From @d
Order by cast(LEFT(SUBSTRING(drug_id, PATINDEX('%[0-9]%', drug_id), LEN(drug_id)), PATINDEX('%[^0-9]%', SUBSTRING(drug_id, PATINDEX('%[0-9]%', drug_id), LEN(drug_id)) + 't') - 1) as int)

This won't work properly if you have a value like "A2-10A" though (or anything with more than one numeric value).

score 0 · Answer 5 · answered Jun 20 '19 at 11:33

If your values are always of the form:

<letter>-<number><optional letter>

Then, you can do:

order by (case when drug_id like '%[A-Z]'
               then len(drug_id) - 1  -- ignore the last character
               else len(drug_id)
          end)
         drug_id

This treats the drug_id as a string, using the length to control the ordering of the numbers. It works when:

the numbers are integers
the numbers are not not zero padded
the prefix is of fixed length

how i can take order by while using nvarchar column in sql

5 Answers5