ORDER BY varchar data type
-
I have a field who's data type is varchar(4). Currently in this field their are numbers but this will change in the future and will hold combinations of numbers and letters. However, I need to get it to sort properly. For example, 1,2,3,4,5,6,7,8,9,10,11,12.....does not order in this manner. It orders like this 1,10,11,12,13,14,15,16,17,18,19,2,21,22,23,24...etc. I need it to order like the first example. There are also values of 3A,3B etc which need to come after 3. I have seen this done before but can not find the proper T-SQL to obtain the desired sort order. Thanks in adavance.
I code therefore I am.....
-
Funny you should mention this today. I got something from Frank Kalis who get it from someone else; but it is pretty slick. It is a function which removes characters. For your example, I used it in the ORDER BY statement.
See if this helps you.
CREATE FUNCTION dbo.RemoveChars( @Input varchar(1000))
RETURNS VARCHAR(100)
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
CREATE TABLE #Test( [Column] varchar(10))
INSERT INTO #Test SELECT '11' AS 'Column'
INSERT INTO #Test SELECT '10' AS 'Column'
INSERT INTO #Test SELECT '2' AS 'Column'
INSERT INTO #Test SELECT '3' AS 'Column'
INSERT INTO #Test SELECT '1' AS 'Column'
INSERT INTO #Test SELECT '3A' AS 'Column'
INSERT INTO #Test SELECT '3B' AS 'Column'
INSERT INTO #Test SELECT '3C' AS 'Column'
SELECT * FROM #Test ORDER BY CONVERT( integer, dbo.RemoveChars( [Column])) ASC
DROP TABLE #Test
DROP FUNCTION dbo.RemoveChars
I wasn't born stupid - I had to study.
-
dag gum! That worked great!!! Thanks!
I code therefore I am....
-
May I add, that I strongly would consider having two columns instead on one?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This doesn't work if the inserts are not done in order:
INSERT INTO #Test Select '2B' as 'Column'
INSERT INTO #Test select '2A' as 'Column'
SELECT * FROM #Test ORDER BY CONVERT( integer, dbo.RemoveChars( [Column])) ASC
Column
----------
1
2
2B
2A
3
3A
3B
3C
10
11
This is probably one of those things that you either follow Frank's suggestion about splitting into two fields or have the client do the work. I think it would be possible to use this approach by adding another function to return only the character data and then a 2 field order by. But depending on your application, this is a lot of overhead.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
I went back an tested it in the application I am using it for. I have numbers from 1 to 70 with some As and Bs(34A,34B) thrown in here and there. I added a few that would make things out of order from when they were inserted(9A, 13A). The result was that the field that I am ordering by( I put the function in the order) was sorted appropriatly. I will let you know if things get wacky as I move forward and more data is entered.
Thank you.
I code therefore I am...
-
Probably too late but I posted this solution in another thread with the same question
order by cast(
(case when patindex('%[a-z]%',[col]) = 0
then [col]
else left([col],patindex('%[a-z]%',[col])-1)
end) as int),[col]
Far away is close at hand in the images of elsewhere.
Anon. -
How about this:
CREATE TABLE #Test
(
col varchar(10)
)
SET NOCOUNT ON
INSERT #test VALUES ('11')
INSERT #test VALUES ('10')
INSERT #test VALUES ('2')
INSERT #test VALUES ('3')
INSERT #test VALUES ('1')
INSERT #test VALUES ('3C')
INSERT #test VALUES ('3B')
INSERT #test VALUES ('3A')
INSERT #test VALUES ('2B')
INSERT #test VALUES ('2A')
SET NOCOUNT OFf
SELECT *
FROM #test
ORDER BY Right( Replicate('0', 10) + col, 10)
DROP TABLE #Test
-
But what when you have data like A2 or the like? This should also be taken into account.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Obviously, there are many ways to do this. Still another method:
CREATE FUNCTION dbo.PadLeft
(
@theField varchar(1000),
@padLen smallint,
@padChar varchar(1)
)
RETURNS varchar(1000)
AS
BEGIN
RETURN Right(Replicate(@padChar, @padLen) + @theField, @padLen)
END
GO
CREATE TABLE #Test
(
col varchar(10)
)
SET NOCOUNT ON
INSERT #test VALUES ('11')
INSERT #test VALUES ('10')
INSERT #test VALUES ('2')
INSERT #test VALUES ('3')
INSERT #test VALUES ('1')
INSERT #test VALUES ('3C')
INSERT #test VALUES ('3B')
INSERT #test VALUES ('3A')
INSERT #test VALUES ('2B')
INSERT #test VALUES ('2A')
SET NOCOUNT OFF
SELECT *
FROM #test
ORDER BY dbo.PadLeft(col, 10, '0')
DROP TABLE #Test
GO
-
Sorry mkeast,
Didn't work the way you expected... here's the output.
col
----------
1
2
3
10
11
2A
2B
3A
3B
3C
I think the original poster wants...
col
----------
1
2
2A
2B
3
3A
3B
3C
10
11
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hey come on frank, stop stirring it
That combination was not asked for
Far away is close at hand in the images of elsewhere.
Anon. -
Frank, I understand the issue you bring up. It depends on the business rules for that the particular application. I think the method that you originally posted requires that non-numeric characters be ignored. In that case, I would use the RemoveChars() UDF. My method assumes numbers followed by letters. I was really addressing the followup question.
The requirements really haven't been fully stated here that account for the various combinations (numbers only, non-numbers only, numbers followed by non-numbers, or any mix of characters, etc).
Again, it all depends on the requirements.
-
Jeff, you are correct. That's what happens when Frank gets everybody excited about an issue. Jumped the gun a bit on that.
-
okay,okay... I don't want to be branded as troublemaker.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply