Sorting with Varchar Field
-
Dear Friends,
How do we sort a varchar field if the field contents doesn't have a format in numbering.I have the field contents in the following format.For eg:
W123
RSA
WE4334
RS12
R1234
When I order such formatted contents using "order by "
keywords,it doesn't sort every contents in the right order.
Is there any method to sort the field contents in the right order.
regards
shown_sunny -
Give us a clue, what order do you want - Numerical, Alphabetical or something completely obscure
.....?Steve
We need men who can dream of things that never were.
-
Steve is right. Depending on how you want it ordered you may want to consider JOINING a 2nd table on this field and then ORDER BY a numeric in the 2nd table that would provide the desired ORDER
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Dear Friends,
Sorry for the incomplete question.
It should be sorted according to the ascii ordering and the RDBMS is SQL SERVER.
regards
shown_sunny
-
Sunny,
Hate to be pedantic but........
A normal ORDER BY statement such as:
SELECT * FROM tblNames ORDER BY NameFirst ASC
Will select in the order 1st character, 1st and 2nd character, 1st, 2nd and 3rd characters. So only on a repeat of the same character / pair of characters / combination of characters, will it look to the next. e.g:
2roger
2roher
2rpger
2rpher
2soger
2spger
If you wanted the ASCII order as a result, you would have to convert each individual character to ASCII - and that (I believe) is what you already have mate.
Unless you are looking for each character, converted to ASCII and then the combined ASCII total for each varchar.
I think you need to decide what order you actually want the results in and give examples pls.
Have fun
Steve
We need men who can dream of things that never were.
-
Dear Steve,
Just check the strings.
It should be coming in the following order.
RKA10
RKA11
RKA100
RMX10
RMX11
RMX100
RSA12
RSA120
RSA139
RSBH01
But what i get is in the following order after ordering.
RKA10
RKA100
RKA11
RMX10
RMX100
RMX11
RSA139
RSA12
RSA120
RSBH01
regards
shown_sunny
-
Hi Sunny,
Could you pls drop an example of your code in. Must be a bit of a weird one this - I put your data into a varchar field in a table and ran it with a straight ORDER BY.
Here's my resultset copied straight out of QA:-
RKA10
RKA100
RKA11
RMX10
RMX100
RMX11
RSA12
RSA120
RSA139
RSBH01
Exactly as expected.......
Steve
We need men who can dream of things that never were.
-
May I add, that your functions could greatly simplified to
CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input
END
GO
SELECT dbo.RemoveChars('RKA10')
DROP FUNCTION dbo.RemoveChars
To remove the characters from the string, just remove the ^
Thank you!!!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
I dont think the UDF is necessary here - just look at the order the data is being sorted into.
If you replicate the ORDER BY with the same data, you get the correct anticipated order.
Have fun
Steve
We need men who can dream of things that never were.
-
Steve,
from the previous posts by the questioner it reads different
It should be coming in the following order.
RKA10
RKA11
RKA100
RMX10
RMX11
RMX100
RSA12
RSA120
RSA139
RSBH01
But I was actually focusing on the UDF. So slightly off-topic maybe
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Correction to myself
In addition to just simply removing the ^ to remove the characters from a string you also need to change the return type of the function:
CREATE FUNCTION dbo.RemoveChars(@Input varchar(6))
RETURNS INT
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input
END
GO
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Determine server's collation, you must.
sp_helpserver -- run it you will ! results, you shall share.
-
Here is a dirty way to do this in SQL 7:
select 'RKA10' as Data into #data from sysobjects where 1=2
union
select 'RKA100'
union
select 'KA11'
union
select 'RMX10'
union
select 'RMX100'
union
select 'RMX11'
union
select 'RSA12'
union
select 'RSA120'
union
select 'RSA1201'
union
select 'RSA139'
union
select 'RSBH01'
select Data,
case when ascii(right(Data,4)) between 49 and 57 then right(Data,4)
else
case when ascii(right(Data,3)) between 49 and 57 then right(Data,3)
else
case when ascii(right(Data,2)) between 49 and 57 then right(Data,2)
else
case when ascii(right(Data,1)) between 49 and 57 then right(Data,1)
else
null
end
end
end
end as Numb,
case when ascii(right(Data,4)) between 49 and 57 then left(Data,len(Data) -4)
else
case when ascii(right(Data,3)) between 49 and 57 then left(Data,len(Data) -3)
else
case when ascii(right(Data,2)) between 49 and 57 then left(Data,len(Data) -2)
else
case when ascii(right(Data,1)) between 49 and 57 then left(Data,len(Data) -1)
else
null
end
end
end
end as Varch
into #order
from #data
Select d.Data from #data d
inner join #order od on d.data=od.data
order by Varch, cast(Numb as int)
drop table #data
drop table #order
-
sorry, I did not read the entire thread.
Obviously, using patterns is much shorter and prettier
select 'RKA10' as Data into #data from sysobjects where 1=2
union
select 'RKA100'
union
select 'KA11'
union
select 'RMX10'
union
select 'RMX100'
union
select 'RMX11'
union
select 'RSA12'
union
select 'RSA120'
union
select 'RSA1201'
union
select 'RSA139'
union
select 'RSBH01'
Select d.Data from #data d
inner join
(
select Data,
left(Data,patindex('%[1-9]%',Data)-1) as Varch, right(Data,len(Data) - patindex('%[1-9]%',Data)+1) as Numb
from #data ) od on d.data=od.data
order by Varch, cast(Numb as int)
drop table #data
-
store data properly, suggests Yoda. Break out these column into two in your database. Combine them for presentation, trivial to do. Separate them for sorting, much harder it is. And much less efficient!
Combining values into 1 column in a table, the path is quick to the dark side. Avoid this you must!
A proper VARCHAR and a propert INT column you will create! Use ideas presented here to help parse your data as is, but in future, fix your structure you will.
Already, have you learned not from your mistake?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply