Answered by:
Order By not working

Question
-
Gurus,
I am trying to do order by on a calculated field but not getting expected results...
Column1
I072
US024
I0155
ILL04
I055
Desired Result:
I 55
I 72
I 155
US 24
ILL 4
How do I get this result?
Thanks in advance.Friday, November 12, 2010 4:44 AM
Answers
-
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Charformat + ' ' + CAST( NUmberformat as varchar(20)) as COl FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1 ORDER BY LEN(CharFormat),Charformat,NUmberformat
- Marked as answer by kellog1 Friday, November 12, 2010 3:35 PM
Friday, November 12, 2010 3:22 PM -
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY LEN(CharFormat),Charformat,Numberformat SELECT Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY LEN(CharFormat),Charformat,Numberformat SELECT Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY Charformat,Numberformat SELECT Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY Charformat,Numberformat
Jon- Marked as answer by kellog1 Friday, November 12, 2010 3:36 PM
Friday, November 12, 2010 3:25 PM
All replies
-
Friday, November 12, 2010 4:53 AM
-
hi kellog,
Yes it was not giving you output b'cos it's combined string and int.try to add sort order column as int and change sort order as you need.
Thanks
Tharindu Dhaneenja
Tharindu Dhaneenja (http://dhaneenja.blogspot.com)Friday, November 12, 2010 5:07 AM -
Hi, try to use this function :
CREATE FUNCTION Search_STR (@s varchar(200)) RETURNS int AS BEGIN declare @i int set @i=0 WHILE @i<=len(@s) and isnumeric(substring(@s,@i,1))=0 Begin set @i=@i+1 End return @i-1 END ----------------------------------------------------------- declare @TT table(string varchar(50)) insert @TT values('I072') insert @TT values('US024') insert @TT values('UZ024') insert @TT values('I0155') insert @TT values('ILL04') insert @TT values('I055') select substring(string,1,dbo.Search_STR(string)) as a,cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as b from @TT order by dbo.Search_STR(string),a,b --results --I 55 --I 72 --I 155 --US 24 --ILL 4
Best regardsFriday, November 12, 2010 5:22 AM -
SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt
from Table
order by rtFriday, November 12, 2010 5:24 AM -
Hi.
Try this please
SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt into #TMP from Table select * FROM #tmp ORDER BY RT <br/> drop table #TMP
karepaFriday, November 12, 2010 5:39 AM -
Hi, try to use this function :
CREATE FUNCTION Search_STR (@s varchar(200)) RETURNS int AS BEGIN declare @i int set @i=0 WHILE @i<=len(@s) and isnumeric(substring(@s,@i,1))=0 Begin set @i=@i+1 End return @i-1 END ----------------------------------------------------------- declare @TT table(string varchar(50)) insert @TT values('I072') insert @TT values('US024') insert @TT values('UZ024') insert @TT values('I0155') insert @TT values('ILL04') insert @TT values('I055') select substring(string,1,dbo.Search_STR(string)) as a,cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as b from @TT order by dbo.Search_STR(string),a,b --results --I 55 --I 72 --I 155 --US 24 --ILL 4
Best regards
If you want one calculed filed, try :select substring(string,1,dbo.Search_STR(string))+' '+cast(cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int) as varchar(50)) as calculed from @TT order by dbo.Search_STR(string),substring(string,1,dbo.Search_STR(string)),cast(substring(string,dbo.Search_STR(string)+1,len(string)-dbo.Search_STR(string)) as int)
Best regardsFriday, November 12, 2010 6:05 AM -
Not sure why ILL should be after US tho, surely it should come after I.
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Column1,LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1),CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) FROM @TT ORDER BY LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1),CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) --I055 --I072 --I0155 --ILL04 --US024
JonFriday, November 12, 2010 8:30 AM -
Probably US came before ILL based on lenght... IF the ordering is based on length,then based on number, this is an example
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Column1,Charformat,NUmberformat FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1 ORDER BY LEN(CharFormat),Charformat,NUmberformat
Friday, November 12, 2010 8:58 AM -
Thanks guys for helping me out. But I am looking for one Calculated field only.
Desired Result Column1 should look like this:
Column1
I 55
I 72
I 155
Friday, November 12, 2010 3:16 PM -
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Charformat + ' ' + CAST( NUmberformat as varchar(20)) as COl FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as NUmberformat)t1 ORDER BY LEN(CharFormat),Charformat,NUmberformat
- Marked as answer by kellog1 Friday, November 12, 2010 3:35 PM
Friday, November 12, 2010 3:22 PM -
DECLARE @TT TABLE(Column1 VARCHAR(50)) INSERT @TT VALUES('I072') INSERT @TT VALUES('US024') INSERT @TT VALUES('I0155') INSERT @TT VALUES('ILL04') INSERT @TT VALUES('I055') SELECT Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY LEN(CharFormat),Charformat,Numberformat SELECT Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY LEN(CharFormat),Charformat,Numberformat SELECT Charformat+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY Charformat,Numberformat SELECT Charformat+SPACE(1)+CAST(Numberformat AS VARCHAR(50)) [Column1] FROM @TT t cross apply (select LEFT(Column1,PATINDEX('%[^a-zA-Z]%', Column1)-1) as Charformat,CAST(RIGHT(Column1,PATINDEX('%[a-zA-Z]%', REVERSE(Column1))-1) AS INT) as Numberformat)t1 ORDER BY Charformat,Numberformat
Jon- Marked as answer by kellog1 Friday, November 12, 2010 3:36 PM
Friday, November 12, 2010 3:25 PM