sorting numbers before letters 1

[progoal]

hi guys i have a problem, i want to select numbers before letters in the same column,:

JS10042
JD10041
MYG107
MR10043
MYG008
RM10044
SB10045



i want something like that, like the "order by ASC"

JS008
IO107
AA10041
BQ10042
QP10043
HM10044
TB10045

can someone help me?

thanks

 

[gmmastros]

This can be tricky because you need to first extract the numbers from the data, convert it to a number data, and use that for the sort. I strongly encourage you to read a blog I wrote several years ago. You can find it here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

In the blog, I show how to create a user defined function that will extract numbers from a string. You could use this function in your query to sort the data the way you want. After creating the dbo.GetNumbers function in your database, you would use it like this:

Select YourColumn
From   YourTable
Order By Convert(int, dbo.GetNumbers(YourColumn))

Please be aware that using a user defined function in an order by clause will cause SQL Server extra work, and will therefore take longer to run. Most likely, the performance will be acceptable unless you are sorting millions of rows. Please give it a try and then respond here with your results.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[progoal]

thanks for the reply..

i dont wanna extract the numbers i want the same sintax but instead letters order by numbers.

 

[MarkSweetland]

As George said, in order to sort by the numerical portion of the column data you'll have to extract it from the data.

But, if the numerical portion always trails the alpha portion of the column, you can use something like this:

set nocount on 

declare @tbl TABLE (code varchar(10))
insert into @tbl (code)
select 'JS10042' 
union all 
select 'JD10041' 
union all 
select 'MYG107' 
union all 
select 'MR10043' 
union all 
select 'MYG008' 
union all 
select 'RM10044'
union all 
select 'SB10045'
union all 
select 'JS008'
union all 
select 'IO107'
union all 
select 'AA10041'
union all 
select 'BQ10042' 
union all 
select 'QP10043' 
union all 
select 'HM10044'
union all 
select 'TB10045'

select code
from @tbl
order by LEN(code), substring(code, PATINDEX('%[0-9]%', code), LEN(code))

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

progoal,

I may not have been perfectly clear with my earlier comment. My advice was to extract the numbers for the purpose of sorting, and only sorting. If you create the user defined function and then run the code I posted (replacing your column and table names), you will see that the data is not changed. You will not see the extracted number, and the extracted number will not be stored anywhere.

I encourage you to take the advice of my earlier post. If you don't like the results, you will only have wasted a couple minutes, but I am 99.9% certain that this is exactly what you are looking for.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Mark,

Your query does not produce the correct results because it is first sorting by the length of the code. Therefore XX999 would soft before YY0000001.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkSweetland]

George,

As usual, you are right. Thought I had something there, but not enough testing....





Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[MarkSweetland]

I must be bored or something:

select code
from @tbl
order by 
[green]-- converted the numerical to int[/green]
convert(int, (substring(code, PATINDEX('%[0-9]%', code),LEN(code)))), 
[green]-- then the alpha part so AS999 comes before XX0000999[/green]
substring(code, PATINDEX('%[A-Z][a-z]%', code), LEN(code))

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[progoal]

mark thanks your solution works for me..

mastro thank you for help too maybe someone needs another solution there it is

 

[gmmastros]

Sorry Mark. Your query returns an error for codes without any numbers.

set nocount on 

declare @tbl TABLE (code varchar(10))
insert into @tbl (code)
select 'JS10042' 
union all 
select ''
union all 
Select '1029'
union all 
Select 'abc'

select code
from @tbl
order by 
-- converted the numerical to int
convert(int, (substring(code, PATINDEX('%[0-9]%', code),LEN(code)))), 
-- then the alpha part so AS999 comes before XX0000999
substring(code, PATINDEX('%[A-Z][a-z]%', code), LEN(code))

The GetNumbers function I pointed to has been tested a thousand different ways and always works. That blog has approximately 19,000 views and not 1 single comment claiming that the code doesn't work. It's tried and true which is why I suggested it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkSweetland]

That blog has approximately 19,000 views and not 1 single comment claiming that the code doesn't work. It's tried and true which is why I suggested it.

I don't doubt it a bit, George.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Olaf Doschke]

What wold be so bad in adding two new fields with that code split into it's alpha and numeric parts?
You could stay with your field and add two computed fields to sort by them. Or, what would be much easier, change your current code field to be a computed field, put together from the letters and digits of two normal fields, as that concatentaion is much easier.

As in:

CREATE TABLE [dbo].[table](
	[alpha] [nvarchar](10) NOT NULL,
	[numeric] [int] NOT NULL,
	[code]  AS ([alpha]+CONVERT([nvarchar](10),[numeric],0))
) ON [PRIMARY]

Now you can sort on numeric,alpha. You might set the computed field "code" to PERSISTED, too.