Sorting Varchar datatype based on INT Data

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)

Sorting Varchar datatype based on INT Data

Author

Topic

malawneh
Starting Member

24 Posts

Posted - 2006-12-19 : 11:06:43

The logic should work so that the field will be sorted like the following example.

1
2
3
Rm. 4
Room 5
6
10
11
12
20
21
22
Rm. 24
rm 25
alpha room
beta room
zeta room

This is an example of the user input in expected output order. This is user entered and is not limited to int data. Hoe do I go about reaching this efficiently?

Michael Alawneh, DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 11:12:51

The #1 thing you need to do in this situation is always be sure that ALL possibilities are represented in your sample data. People might spend hours writing a solution that works for the situations you present, but then suddenly we learn that "room one" needs to sort as if it were "room 1", and so on. So, that's the first order of business -- you must assure us that as we present solutions to help you, you will not be giving us a "moving target" by adding in new possibilities as you go. this happens WAAAY to often around here ....

now, the long term solution, if the sorting of this data is important, is to enforce proper data types going forward. If we help you parse this column into a numeric value, you must comply by storing the numeric value in a true numeric column, which will be updated/maintained going forward, and by which further sorting and indexes and all that can be done. If the data is important enough that it needs to sort a certain way, then it needs to be stored a certain way as well.
- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:30:45

[code]declare @t table (data varchar(100))

insert @t
select '1' union all
select '10' union all
select '11' union all
select '12' union all
select '2' union all
select '20' union all
select '21' union all
select '22' union all
select '3' union all
select '6' union all
select 'alpha room' union all
select 'beta room' union all
select 'c12 room' union all
select 'rm 25' union all
select 'Rm. 24' union all
select 'Rm. 4' union all
select 'Room 5' union all
select 'zeta room'

select t.data
from (
select w.p1,
w.data,
patindex('%[^0-9]%', substring(w.data, w.p1, 8000)) p2
from (
select patindex('%[0-9]%', data) p1,
data
from @t
) w
) t
order by case
when t.p1 > 0 and t.p2 = 0 then cast(substring(t.data, t.p1, 8000) as int)
when t.p1 > 0 and t.p2 > 0 then cast(substring(t.data, t.p1, t.p2 - t.p1 + 1) as int)
else 2147483647
end[/code]

Peter Larsson
Helsingborg, Sweden

malawneh
Starting Member

24 Posts

Posted - 2006-12-19 : 11:37:23

This is a varchar column and it will remain so because the developers do not want to provide the end users with another field to enter the potentialy numeric values.
The rule for the sort order is if there is a numeric value anywhere in the field strip it out and use it for the order by, if the field has no numeric value sort based on the alpha sort.
The above data in the samlple set is actual data through user entry.
Regardless of spelling all we realy care about is the numeric portion first then the alpha sort takes over.

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:44:31

Oh, man... Why do I keep to bother?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 12:41:27

quote:
Originally posted by malawneh

This is a varchar column and it will remain so because the developers do not want to provide the end users with another field to enter the potentialy numeric values.
The rule for the sort order is if there is a numeric value anywhere in the field strip it out and use it for the order by, if the field has no numeric value sort based on the alpha sort.
The above data in the samlple set is actual data through user entry.
Regardless of spelling all we realy care about is the numeric portion first then the alpha sort takes over.

Michael Alawneh, DBA

You're the DBA, right? Then you explain to them that this is not how databases work. You need something called data integrity. If the numeric value has a meaning, if data needs to be sorted by it, then it needs to be stored in the database properly, potentially indexed as well. If the user enters a string via the UI and it needs to be parsed, it SHOULD BE PARSED AT THE CLIENT AT THE POINT OF ENTRY and only valid data should be stored in your database. The client application is much, much, much more equiped to do string processing/parsing rather than a relational database ! And doing it once, at the time of entry, it much, much more efficient than doing it over and over by the RDBMS each time the database is queried.

Never confuse the UI with the database layer -- two different things. You should never store data in a certain manner because the UI presents it that way. If the developers feel it is the database's job to parse strings into numeric tokens, then it is time to get some new developers ......

- Jeff

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-12-19 : 12:46:32

"If the developers feel it is the database's job to parse strings into numeric tokens, then it is time to get some new developers ......"...easier way to get the message acrosss...ask the developers to come up with a solution!!!!!....using SQL....

That'll cure them of their flexibility!!

malawneh
Starting Member

24 Posts

Posted - 2006-12-19 : 13:18:50

I have a solution for this but it is slow. I need to speed it up. The code readds as follows.

CLOSE ClassRoomADECursor
DEALLOCATE ClassRoomADECursor

Declare @Count int
, @Len Varchar(20)

declare SortCur cursor for
select ClassRoomAbbr from @TempOutputClassroomADE

open SortCur
fetch next from SortCur into @Len
while @@FETCH_STATUS = 0
begin
set @Count=0
while @Count < Len(@Len)
begin
Set @Count = @Count+1
if isnumeric(substring(@Len,@Count,1))=1
break
else
continue
end

update @TempOutputClassroomADE set Sort = case when isnumeric (Right(@Len,1))= 1 then substring(@Len,@Count,Len(@Len)-(@Count-1))
else
@Len
end
where ClassRoomAbbr= @Len

fetch next from SortCur into @Len
End
close SortCur
deallocate SortCur

update @TempOutputClassroomADE set [Count] = cast(sort as int) where isnumeric(Sort)=1
update @TempOutputClassroomADE set [Count] = (Select max([Count])+1 from @TempOutputClassroomADE) where isnumeric(Sort)=0
update @TempOutputClassroomADE set [Count] = (select max([Count])+2 from @TempOutputClassroomADE) where ClassRoomAbbr='ALL'

SELECT ClassRoomAbbr
, IsNull(Need, '') AS Need
, IsNull(Time,'') AS Time
, CurrentPeriod
, AdjustmentFactor
, Adjusted
FROM @TempOutputClassroomADE
ORDER BY [Count],ClassRoomAbbr

Is there another way, or can someone see where I can improve the effiency of this?

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 13:22:42

Yes, I posted one to you at 12/19/2006 : 11:30:45, almost two hours ago...

Peter Larsson
Helsingborg, Sweden

malawneh
Starting Member

24 Posts

Posted - 2006-12-19 : 14:31:50

here is the output I get when using the query from 'PESO'. It works great when only considering the numeric value. but it should also sort secondly on the char value.

data
---------------------------------------------------------------------------------------------------
#1
RM 1
Roland Room 1
D Classroom1
1
C1
1
Roland Room 1
C1
C1
C1
C1
C2
C2
C2
C2
C2
D Classroom2
#2
#3
C3
C3
C3
C4
#4
C5
#11
#11
Rm 121
Rm 221
Rm 221
Rm 228
Rm 1001
RM 1112
Test

DC
SPS Class Room

(39 row(s) affected)

so the output if only taking the ("1") values into consideration should sort as follows.
data
---------------------------------------------------------------------------------------------------
#1
1
1
C1
C1
C1
C1
C1
D Classroom1
RM 1
Roland Room 1
Roland Room 1

Thank you all for your help.

Michael Alawneh, DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 14:37:54

I thought you should be able to add "data" to the sort order.
The only thing differ between this query and the one above, is the code at bottom highlighted in red.

declare @t table (data varchar(100))

insert	@t
select	'#1' union all
select	'RM 1' union all
select	'Roland Room 1' union all
select	'D Classroom1' union all
select	'1' union all
select	'C1' union all
select	'1' union all
select	'Roland Room 1' union all
select	'C1' union all
select	'C1' union all
select	'C1' union all
select	'C1' union all
select	'C2' union all
select	'C2' union all
select	'C2' union all
select	'C2' union all
select	'C2' union all
select	'D Classroom2' union all
select	'#2' union all
select	'#3' union all
select	'C3' union all
select	'C3' union all
select	'C3' union all
select	'C4' union all
select	'#4' union all
select	'C5' union all
select	'#11' union all
select	'#11' union all
select	'Rm 121' union all
select	'Rm 221' union all
select	'Rm 221' union all
select	'Rm 228' union all
select	'Rm 1001' union all
select	'RM 1112' union all
select	'Test'

select		t.data
from		(
			select		w.p1,
					w.data,
					patindex('%[^0-9]%', substring(w.data, w.p1, 8000)) p2
			from		(
						select		patindex('%[0-9]%', data) p1,
								data
						from		@t
					) w
		) t
order by	case
			when t.p1 > 0 and t.p2 = 0 then cast(substring(t.data, t.p1, 8000) as int)
			when t.p1 > 0 and t.p2 > 0 then cast(substring(t.data, t.p1, t.p2 - t.p1 + 1) as int)
			else 2147483647
		end,
		data

Peter Larsson
Helsingborg, Sweden

malawneh
Starting Member

24 Posts

Posted - 2006-12-19 : 14:48:13

Thank you so much it was a complete oversight on my part. I have been strugling with this for some time now. My brain is fried.
Once again thank you.

Michael Alawneh, DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources