How to sort alphanumeric values
-
I have one field which stores alphanumeric values like
Schedule1
Schedule13
Schedule2
Schedule3
Schedule14,etc..
If I sort this in my query it will sort as follows
Schedule1
Schedule13
Schedule14
Schedule2
Schedule3
But I want records in following order
Schedule1
Schedule2
Schedule3
Schedule13
Schedule14
Is it possible to sort as above...?
-
Please post the query in which you sorted .With an ORDER BY clause you can get the results you desired.
-
Hope you're looking for this...
SELECT {ColumnName}
FROM {TableName}
WHERE {ColumnName} LIKE 'Schedule%'
ORDER BY CAST(REPLACE({ColumnName},'Schedule','') AS INT)
-
this is the query
SELECT [ScheduleID],ScheduleName FROM [Schedule] ORDER BY schedulename ASC
and this is the result
1Schedule1
3Schedule12
4Schedule14
2Schedule2
5Schedule31
I want output in this format
1Schedule1
2Schedule2
3Schedule12
4Schedule14
5Schedule31
-
And I just specified name as "schedule"....
It may be some other name also........So I cant find that name dynamically...
-
Ok
Try out this..SELECT [ScheduleID],ScheduleName
FROM [Schedule]
ORDER BY CAST(CASE
WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,7)
WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,6)
WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,5)
WHEN ScheduleName LIKE '%[0-9][0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,4)
WHEN ScheduleName LIKE '%[0-9][0-9][0-9]' THEN RIGHT(ScheduleName ,3)
WHEN ScheduleName LIKE '%[0-9][0-9]' THEN RIGHT(ScheduleName ,2)
WHEN ScheduleName LIKE '%[0-9]' THEN RIGHT(ScheduleName ,1)
ELSE NULL END AS INT)
Note: It would be better to implement the whole CASE part as a scalar function to completely encapsulate the logic and simply re-use the function wherever such sorting required.
-
Thanks Pawan... its working......:-D
-
HEY DUDE,
ITS WORKING VERY FINE,,,GOOD ONE GRETA...
I HAVE NEVER TRIED THIS WAY SO IM VERY GALD TO SEE IT..
REGARDS,
mITHUN
-
Hi Manohar,
Try this
declare @abc table
(
name1 varchar(10)
)
insert into @abc values ('NAME1')
insert into @abc values ('NAME10')
insert into @abc values ('NAME11')
insert into @abc values ('NAME111')
insert into @abc values ('NAME2')
insert into @abc values ('NAME5')
insert into @abc values ('NAME3')
insert into @abc values ('NAME4')
insert into @abc values ('NAME6')
insert into @abc values ('NAME7')
--
select * from @abc
ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)
ARUN SAS
-
Thanks Arun....
Your code works fine.........
I will implement this in my procedure....:-P
-
arun.sas (4/9/2009)
Hi Manohar,Try this
declare @abc table
(
name1 varchar(10)
)
insert into @abc values ('NAME1')
insert into @abc values ('NAME10')
insert into @abc values ('NAME11')
insert into @abc values ('NAME111')
insert into @abc values ('NAME2')
insert into @abc values ('NAME5')
insert into @abc values ('NAME3')
insert into @abc values ('NAME4')
insert into @abc values ('NAME6')
insert into @abc values ('NAME7')
--
select * from @abc
ORDER BY cast (substring(name1,(PATINDEX('%[0-9]%',name1)),len(name1))as int)
ARUN SAS
HAH you beat me to it. I was trying the same thing myself. The solution posted can be used is all such situations.
"Keep Trying"
-
Arun....
Ur logic works only in the case of alphanumeric values... I throws error in case of string
The error is
------
Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'NAM' to data type int.
-
hi manohar,
if ur values are going to be mixed of string and alphanumeric and if u want to take care of order of alphanumeric then try this one
ORDER BY cast (case when (PATINDEX('%[0-9]%',SCH_NAME))>0 then substring(SCH_NAME,(PATINDEX('%[0-9]%',SCH_NAME)),len(SCH_NAME) )
else 0 end as int)
else
the first solution u got would still work okkkk
thanks
Mithun
-
manohar (4/9/2009)
Arun....Ur logic works only in the case of alphanumeric values... I throws error in case of string
The error is
------
Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'NAM' to data type int.
Yeah but it's easy enough to fix:
SET NOCOUNT ONDECLARE @abc TABLE(name1 varchar(10))insert into @abc values ('NAME1')insert into @abc values ('NAME10')insert into @abc values ('NAME11')insert into @abc values ('NAME111')insert into @abc values ('NAME2')insert into @abc values ('NAME5')insert into @abc values ('NAME3')insert into @abc values ('NAME4')insert into @abc values ('NAME6')insert into @abc values ('NAME7')insert into @abc values ('NAME_')--SELECT *FROM @abcORDER BY RIGHT('0000' + SUBSTRING(name1, ISNULL(NULLIF(PATINDEX('%[0-9]%',name1), 0), LEN(name1)+1), LEN(name1)), 4)“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
It seems like all the function calls are a bit overkill IMO, unless I'm missing how they handle some edge-case.
A simple
ORDER BY len(column), column usually works for me for the generic case of "sorting alphanumeric values by numeric sorting".
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply