# 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?

Friday, November 12, 2010 4:44 AM

• ```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 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 Friday, November 12, 2010 3:36 PM
Friday, November 12, 2010 3:25 PM

### All replies

• Plz post the query you are using...
~Manu
http://sqlwithmanoj.wordpress.com
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 regards
Friday, November 12, 2010 5:22 AM
• SELECT LEFT(Col1, 3) +' '+ REPLACE(SUBSTRING(Col1,2,4),'0','') rt
from Table
order by rt
Friday, November 12, 2010 5:24 AM
• Hi.

```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
Friday, 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 regards
Friday, 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
```

Jon
Friday, 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 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 Friday, November 12, 2010 3:36 PM
Friday, November 12, 2010 3:25 PM