4

In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort order.

  • Issue No. 1

For example, contains like following data

1-2
1
3-5
6
1-2A
6-12
7-8
9-12D
9-12
17
9-12B
19
20-30
15-18

Now I want to order the data as below

1
1-2
1-2A
3-5
6
6-12
7-8
9-12
9-12B
9-12D
15-18
17
19
20-30
  • Issue No. 2

the bellow value How to sort alphanumeric?

PT15-8-2

PT15-12-10

PT15-12-3

PT15-12-572-575

PT15-12-31

PT11-96

PT11-133-36

PT11-133

PT16-136

PT8-644

PT09-655

PT9-655

PT12-752

PT14-752

PT13-752

PT14-753

PT10-778

PT13-977-978

PT13-979-988

Can anyone please tell me how should I write the query?

  • is before - value would always intger – wiretext Oct 8 '15 at 9:22
  • please check my updated answer – wiretext Oct 8 '15 at 10:21
  • Some time Only one digit coming at the time getting error such as "Invalid length parameter passed to the LEFT or SUBSTRING function." Please anyone help me... Examplle 1-2, 3-5, 2, 3-5A, 1-2A – M.I.M.F Oct 12 '15 at 20:31
  • Is there any validation to check sorting value is integer or varchar value. – M.I.M.F Oct 13 '15 at 5:36
  • FYI: I filed a Microsoft Connect suggestion that this type of sorting be available natively, just as it is in C++: Support "natural sorting" / DIGITSASNUMBERS as a Collation option. Voting for it won't guarantee that they implement it, but it does help indicate how much people actually want that feature :) – Solomon Rutzky Aug 24 '16 at 14:44
3

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

enter image description here

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES 
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE 
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val 
    END ,val

RESULT:

enter image description here

  • 3
    I'd be careful with this, since you're storing "numbers" in a string column, there is nothing to stop someone from doing this: insert @temp values('6A-12'); - so, you need to test that substring before blindly converting it to an integer. The best thing to do would be to split it up instead of storing them together; validation can happen at insert/update time, and you can always easily make a computed column that performs the concatenation. – Aaron Bertrand Oct 8 '15 at 12:50
  • @tinka thanks alot.. you save my day. another think i want to know how did u get the answer.. can you explain the answered query.. is there anyway or techniques to generate queries... – M.I.M.F Oct 13 '15 at 9:13
  • @user1663855 nothing any special just try to separate each part and apply as per my understanding of SQL – wiretext Oct 13 '15 at 10:09
  • @user1663855 Please remove your obsolete comments from this thread. Comments are meant to be temporary post-it-notes, that should be removed once they are addressed. – Erik Oct 14 '15 at 4:22
  • @tinka your sort order is correct but one issue such as "Error converting data type varchar to int." please check the 2nd issue in the question i have modified and added the sorting order part i want...thanks – M.I.M.F Aug 24 '16 at 5:02
-4

Sorry for that

I think below query is fine for that

SELECT * FROM table_name ORDER BY cast(CartonNo as decimal(38,10)) asc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.