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.

Answer 1

Plz post the query you are using...

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 2

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)

Answer 3

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

Answer 4

SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt
from Table
order by rt

Answer 5

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

---

karepa

Answer 6

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 regards

Answer 7

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

---

Jon

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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