order by mixed number and alpha

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

order by mixed number and alpha

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-11-09 : 10:28:46

I would like to sort the following data in the order that I have put below. Or at least something that looks right to humans not machines.

6
33
75
90
A110
A310
A610
B3
B5
B6
B7
B8
B9
B10
B11
B12
DB 6
DB 7
DB 9
DBS
FB1
FB2
FB13
FB13-1
FB34
FB100
FB101
FB101 AC

Can some one point me in the right direction.
--
David

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 10:45:52

Forgive my sarcasm, but based on what you've posted, I'd have to say - it's already sorted in the order you want, so what's the question?

You'll need to post more about this data, is it all in a single column in a single table and if so what does the table look like, data-types of the column/s etc.
Essentially if you treat the data as character data, then it will sort in that order, but I can't tell you any more than that without more from you.

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-11-09 : 11:00:08

:)
The order is a manual order that I typed it. is actually looks more like.

33
6
75
90
A110
A310
A610
B10
B11
B12
B3
B5
B6
B7
B8
B9
DB 6
DB 7
DB 9
DBS
FB1
FB100
FB101
FB101 AC
FB13
FB13-1
FB2
FB34

Look at the numbers the 33 is before 6 and FB2 comes after FB13-1. this is not a natural order for humans but OK for computers.

It is a simple table. I don't mind putting in a new column for sorting.

The column is a varchar(20).

--
David

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 11:20:28

Then this should do it - it will only work for data similar to what gave (it will not work on decimals for example)

declare @TestSort table (col varchar(20))
insert @TestSort values ('33')
insert @TestSort values ('6')
insert @TestSort values ('75')
insert @TestSort values ('90')
insert @TestSort values ('A110')
insert @TestSort values ('A310')
insert @TestSort values ('A610')
select * from @TestSort 
order by 
case 
	when isnumeric(col) = 1 then replicate(' ', 20 - len(col)) + col
	else col
end

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-11-09 : 11:45:14

Well that is Half of it which I managed myself. The fun comes with a letter (or two) in front of the number.

try

declare @TestSort table (col varchar(20))
insert @TestSort values ('B10')
insert @TestSort values ('B11')
insert @TestSort values ('B1')
insert @TestSort values ('B2')
select * from @TestSort
order by
case
when isnumeric(col) = 1 then replicate(' ', 20 - len(col)) + col
else col
end

It comes back with
B1
B10
B11
B2
What I really need is

B1
B2
B10
B11

So I think that I need to split the string between the letters and the numbers and then pad it out.

The fun then comes with

FB1
FB2
FB13
FB13-1
FB34
FB100

--
David

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-09 : 12:07:22

try this :


declare @TestSort table (col varchar(20))
insert @TestSort values ('33')
insert @TestSort values ('6')
insert @TestSort values ('75')
insert @TestSort values ('90')
insert @TestSort values ('A110')
insert @TestSort values ('A310')
insert @TestSort values ('A610')
insert @TestSort values ('B10')
insert @TestSort values ('B11')
insert @TestSort values ('B1')
insert @TestSort values ('B2')
insert @TestSort values ('FB1')
insert @TestSort values ('FB2')

select	col
from
(
	select 	col, 
		alpha	= left(col, patindex('%[0-9]%', col) - 1),
		num 	= substring(col, patindex('%[0-9]%', col), len(col))
	from 	@TestSort 
) t
order by case when isnumeric(col) = 1 then 0 else 1 end,
	 alpha, len(alpha), len(num)

pootle_flump

1064 Posts

Posted - 2006-11-09 : 12:38:14

KH - I played with your code a tiny bit (I don't think it was quite there).

select	col
from
(
	select 	col, 
		alpha	= left(col, patindex('%[0-9]%', col) - 1),
		num 	= CAST(substring(col, patindex('%[0-9]%', col), len(col)) AS Int)
	from 	@TestSort 
) t
order by case when isnumeric(col) = 1 then 0 else 1 end,
	 alpha--, --len(alpha)
	, num--, len(num)

EDIT - corrected some minor errors

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-11-09 : 12:39:44

My bad.

pootle_flump

1064 Posts

Posted - 2006-11-09 : 12:47:14

That is fan-dabi-dozi (as Jimmy used to say)

EDIT - you deleted your reply - is that because of the char-num-char entries?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-11-09 : 15:38:40

I deleted my reply because it fails to sort the variable length strings correctly.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-09 : 15:46:18

Why even bother to CAST?

declare @TestSort table (col varchar(20))
insert @TestSort values ('33')
insert @TestSort values ('6')
insert @TestSort values ('75')
insert @TestSort values ('90')
insert @TestSort values ('A110')
insert @TestSort values ('A310')
insert @TestSort values ('A9')
insert @TestSort values ('A610')
insert @TestSort values ('B10')
insert @TestSort values ('B11')
insert @TestSort values ('B1')
insert @TestSort values ('B2')
insert @TestSort values ('FB1')
insert @TestSort values ('FB2')

select	col
from
(
	select 	col, 
		alpha	= left(col, patindex('%[0-9]%', col) - 1),
		num 	= substring(col, patindex('%[0-9]%', col), len(col))
	from 	@TestSort 
) t
order by alpha, len(num), num

Those records where alpha is empty string are already numeric.

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2006-11-09 : 16:19:57

I had commented out the len(num). You are quite correct - with it there is no need. And it would choke on some of the OPs original values.

It still isn't quite there though:

INSERT INTO @TestSort 
SELECT	'FB102' UNION ALL
SELECT	'FB101 AC'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 03:54:33

Try this.

declare @TestSort table (col varchar(20))

insert @TestSort values ('33')
insert @TestSort values ('6')
insert @TestSort values ('75')
insert @TestSort values ('90')
insert @TestSort values ('A110')
insert @TestSort values ('A310')
insert @TestSort values ('A9')
insert @TestSort values ('X')
insert @TestSort values ('A610')
insert @TestSort values ('B10')
insert @TestSort values ('B11')
insert @TestSort values ('9C')
insert @TestSort values ('B1')
insert @TestSort values ('B2')
insert @TestSort values ('FB1')
insert @TestSort values ('FB2')
insert @TestSort values ('FB13')
insert @TestSort values ('FB13-1')
insert @TestSort values ('FB102')
insert @TestSort values ('FB101 AC')

select		col
from		@testsort
order by	dbo.fnTestSort(col)

/*
CREATE FUNCTION dbo.fnTestSort
(
	@ColValue VARCHAR(20)
)
RETURNS VARCHAR(80)
AS

BEGIN
	DECLARE @p1 VARCHAR(20),
		@p2 VARCHAR(20),
		@p3 VARCHAR(20),
		@p4 VARCHAR(20),
		@Index TINYINT

	IF @ColValue LIKE '[a-z]%'
		SELECT	@Index = PATINDEX('%[0-9]%', @ColValue),
			@p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 20), 20),
			@ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 20) END
	ELSE
		SELECT	@p1 = REPLICATE(' ', 20)

	SELECT	@Index = PATINDEX('%[^0-9]%', @ColValue)

	IF @Index = 0
		SELECT	@p2 = RIGHT(REPLICATE(' ', 20) + @ColValue, 20),
			@ColValue = ''
	ELSE
		SELECT	@p2 = RIGHT(REPLICATE(' ', 20) + LEFT(@ColValue, @Index - 1), 20),
			@ColValue = SUBSTRING(@ColValue, @Index, 20)

	SELECT	@Index = PATINDEX('%[0-9,a-z]%', @ColValue)

	IF @Index = 0
		SELECT	@p3 = REPLICATE(' ', 20)
	ELSE
		SELECT	@p3 = LEFT(REPLICATE(' ', 20) + LEFT(@ColValue, @Index - 1), 20),
			@ColValue = SUBSTRING(@ColValue, @Index, 20)

	IF PATINDEX('%[^0-9]%', @ColValue) = 0
		SELECT	@p4 = RIGHT(REPLICATE(' ', 20) + @ColValue, 20)
	ELSE
		SELECT	@p4 = LEFT(@ColValue + REPLICATE(' ', 20), 20)

	RETURN	@p1 + @p2 + @p3 + @p4

END
*/

Peter Larsson
Helsingborg, Sweden

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-11-10 : 04:20:57

Hi Peter

Tyied and liked.

I have a few other permations and combinations together with come different columns, but with this approach it means that I can anything that I need.

Thanks.

--
David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:32:43

Good. Anyhow it will give you an idea of how to proceed.

Peter Larsson
Helsingborg, Sweden

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-11-21 : 07:19:40

Hi Peter

I have only just got back to this on and I thought I would pass on what I have done.
Just for info I have changed it to

--------------------8<--------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[fnSort]
(
@ColValue VARCHAR(60)
)
RETURNS VARCHAR(320)
AS
BEGIN
DECLARE @p1 VARCHAR(60),
@p2 VARCHAR(60),
@p3 VARCHAR(60),
@p4 VARCHAR(60),
@Index TINYINT

-- Part 1
IF @ColValue LIKE '[a-z]%'
begin
select @Index = PATINDEX('%[0-9]%', @ColValue)
, @p1 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p1 = left(dbo.GetCharacters(@p1,'a-zA-Z')+ replicate(' ',60),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end
ELSE
begin
select @Index = PATINDEX('%[a-zA-Z]%', @ColValue)
, @p1 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p1 = right(replicate(' ',60)+dbo.GetCharacters(@p1,'0-9'),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end

-- Part 2

IF @ColValue LIKE '[a-z]%'
begin
select @Index = PATINDEX('%[0-9]%', @ColValue)
, @p2 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p2 = left(dbo.GetCharacters(@p2,'a-zA-Z')+ replicate(' ',60),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end
ELSE
begin
select @Index = PATINDEX('%[a-zA-Z]%', @ColValue)
, @p2 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p2 = right(replicate(' ',60)+dbo.GetCharacters(@p2,'0-9'),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end

-- Part 3

IF @ColValue LIKE '[a-z]%'
begin
select @Index = PATINDEX('%[0-9]%', @ColValue)
, @p3 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p3 = left(dbo.GetCharacters(@p3,'a-zA-Z')+ replicate(' ',60),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end
ELSE
begin
select @Index = PATINDEX('%[a-zA-Z]%', @ColValue)
, @p3 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p3 = right(replicate(' ',60)+dbo.GetCharacters(@p3,'0-9'),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end

-- Part 4

IF @ColValue LIKE '[a-z]%'
begin
select @Index = PATINDEX('%[0-9]%', @ColValue)
, @p4 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p4 = left(dbo.GetCharacters(@p4,'a-zA-Z')+ replicate(' ',60),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end
ELSE
begin
select @Index = PATINDEX('%[a-zA-Z]%', @ColValue)
, @p4 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p4 = right(replicate(' ',60)+dbo.GetCharacters(@p4,'0-9'),60)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 60) END
end

RETURN @p1 + @p2 + @p3 + @p4 +@ColValue

END
--------------------8<--------------------
The getCharacter function strips out bits I don't want. and looks like.
--------------------8<--------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetCharacters](@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN

While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

Return @myString
END
--------------------8<--------------------
Thanks for the help.

--
David

RTRT2
Starting Member

1 Post

Posted - 2007-12-20 : 15:52:43

quote:

Originally posted by Peso

Try this.

declare @TestSort table (col varchar(20))

insert @TestSort values ('33')
insert @TestSort values ('6')
insert @TestSort values ('75')
insert @TestSort values ('90')
insert @TestSort values ('A110')
insert @TestSort values ('A310')
insert @TestSort values ('A9')
insert @TestSort values ('X')
insert @TestSort values ('A610')
insert @TestSort values ('B10')
insert @TestSort values ('B11')
insert @TestSort values ('9C')
insert @TestSort values ('B1')
insert @TestSort values ('B2')
insert @TestSort values ('FB1')
insert @TestSort values ('FB2')
insert @TestSort values ('FB13')
insert @TestSort values ('FB13-1')
insert @TestSort values ('FB102')
insert @TestSort values ('FB101 AC')

select		col
from		@testsort
order by	dbo.fnTestSort(col)

/*
CREATE FUNCTION dbo.fnTestSort
(
	@ColValue VARCHAR(20)
)
RETURNS VARCHAR(80)
AS

BEGIN
	DECLARE @p1 VARCHAR(20),
		@p2 VARCHAR(20),
		@p3 VARCHAR(20),
		@p4 VARCHAR(20),
		@Index TINYINT

	IF @ColValue LIKE '[a-z]%'
		SELECT	@Index = PATINDEX('%[0-9]%', @ColValue),
			@p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 20), 20),
			@ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 20) END
	ELSE
		SELECT	@p1 = REPLICATE(' ', 20)

	SELECT	@Index = PATINDEX('%[^0-9]%', @ColValue)

	IF @Index = 0
		SELECT	@p2 = RIGHT(REPLICATE(' ', 20) + @ColValue, 20),
			@ColValue = ''
	ELSE
		SELECT	@p2 = RIGHT(REPLICATE(' ', 20) + LEFT(@ColValue, @Index - 1), 20),
			@ColValue = SUBSTRING(@ColValue, @Index, 20)

	SELECT	@Index = PATINDEX('%[0-9,a-z]%', @ColValue)

	IF @Index = 0
		SELECT	@p3 = REPLICATE(' ', 20)
	ELSE
		SELECT	@p3 = LEFT(REPLICATE(' ', 20) + LEFT(@ColValue, @Index - 1), 20),
			@ColValue = SUBSTRING(@ColValue, @Index, 20)

	IF PATINDEX('%[^0-9]%', @ColValue) = 0
		SELECT	@p4 = RIGHT(REPLICATE(' ', 20) + @ColValue, 20)
	ELSE
		SELECT	@p4 = LEFT(@ColValue + REPLICATE(' ', 20), 20)

	RETURN	@p1 + @p2 + @p3 + @p4

END
*/

Peter Larsson
Helsingborg, Sweden

Thank you so much for this post!
We had been cracking our heads over a sort function for way too long... and this worked like a charm!

Thanks for your help :)

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-07 : 16:29:27

I have simplified it further. this will also work if people want to sort a field which has any data in it including special charatcers.

Create FUNCTION [dbo].[fnSort]
(
@ColValue VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @ResStr varchar(4000),
@p1 VARCHAR(4000),
@Index TINYINT,
@count bigint

select @colvalue=dbo.getcharacters(@colvalue,'a-z0-9')

while @ColValue<>''
begin
select @p1=''
IF @ColValue LIKE '[A-Z]%'
begin
select @Index = PATINDEX('%[0-9]%', @ColValue)
, @p1 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p1 = left(@p1+ replicate(' ',5),len(@p1))
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, len(@ColValue)) END
end
ELSE
begin
select @Index = PATINDEX('%[A-Z]%', @ColValue)
, @p1 = CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END
select @p1 = right(replicate(' ',5)+@p1,5)
, @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, len(@ColValue)) END
end
set @resstr=isnull(@resstr,'')+ isnull(@p1,'')

end
return @resstr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources