April 8, 2009 at 11:56 pm
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...?
April 9, 2009 at 12:10 am
Please post the query in which you sorted .With an ORDER BY clause you can get the results you desired.
April 9, 2009 at 12:39 am
Hope you're looking for this...
SELECT {ColumnName}
FROM {TableName}
WHERE {ColumnName} LIKE 'Schedule%'
ORDER BY CAST(REPLACE({ColumnName},'Schedule','') AS INT)
April 9, 2009 at 12:50 am
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
April 9, 2009 at 12:53 am
And I just specified name as "schedule"....
It may be some other name also........So I cant find that name dynamically...
April 9, 2009 at 1:14 am
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.
April 9, 2009 at 1:27 am
Thanks Pawan... its working......:-D
April 9, 2009 at 1:37 am
HEY DUDE,
ITS WORKING VERY FINE,,,GOOD ONE GRETA...
I HAVE NEVER TRIED THIS WAY SO IM VERY GALD TO SEE IT..
REGARDS,
mITHUN
April 9, 2009 at 2:08 am
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
April 9, 2009 at 2:23 am
Thanks Arun....
Your code works fine.........
I will implement this in my procedure....:-P
April 9, 2009 at 3:14 am
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"
April 9, 2009 at 3:37 am
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.
April 9, 2009 at 6:58 am
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
April 9, 2009 at 7:32 am
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 ON
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')
insert into @abc values ('NAME_')
--
SELECT *
FROM @abc
ORDER BY RIGHT('0000' + SUBSTRING(name1, ISNULL(NULLIF(PATINDEX('%[0-9]%',name1), 0), LEN(name1)+1), LEN(name1)), 4)
For 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
April 9, 2009 at 10:52 am
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 19 total)
You must be logged in to reply to this topic. Login to reply