May 1, 2012 at 12:03 am
Hi,
I am new in sql.
i have column rack_location which is having rack number of ware house.
data type is varchar(50).
i am inserting rack number like c1,c2,c3,c10,c12,c25,c100,c102 .....
when i am using order by
i am getting
c1
c10
c100
c102
c12
c2
c25
c3
but i need
c1
c2
c3
c10
c12
c25
c100
c102
plz help me
thanks in advance
May 1, 2012 at 1:42 am
One way to do this:
CREATE TABLE #Temp(
Rack_LocationVarChar(50)
);
INSERT INTO #Temp(Rack_Location)
VALUES('c1'),
('c10'),
('c25'),
('c100'),
('c3'),
('c102'),
('c12'),
('c2');
select *
from #Temp
order by Rack_Location;
select *
from #Temp
order by CAST(RIGHT(rack_location,LEN(rack_location) - 1) AS INT);
Because (with your example data) the very first character is the only character that is not a number, the 'ORDER BY' just looks at all characters except for the first and then converts it to an integer, which will then sort as a number. Because the original column was character, just stripping of the first character would not work because it would still be treated as a varchar field and the result would be the same as you alreay have.
If the format is not always as you have given (for example - it may be 'aa123') then it becomes a little more involved.
Of course, more elegeant solutions will exist, but this is the one that came to me immediately.
Edited for typo.
May 1, 2012 at 1:44 am
Hi
Assuming you only ever have one non numeric character at the beginning this should do the trick:
DECLARE @TEMP AS TABLE
(VALS VARCHAR(10))
INSERT INTO @TEMP
SELECT 'c1' union all
SELECT 'c10' union all
SELECT 'c100' union all
SELECT 'c102' union all
SELECT 'c12' union all
SELECT 'c2' union all
SELECT 'c25' union all
SELECT 'c3'
SELECT *
FROM @TEMP
ORDER BY
CAST(RIGHT(VALS,LEN(VALS) -1) AS INT)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 1, 2012 at 1:47 am
BrainDonor (5/1/2012)
One way to do this:
CREATE TABLE #Temp(Rack_LocationVarChar(50)
);
INSERT INTO #Temp(Rack_Location)
VALUES('c1'),
('c10'),
('c25'),
('c100'),
('c3'),
('c102'),
('c12'),
('c2');
select *
from #Temp
order by Rack_Location;
select *
from #Temp
order by CAST(RIGHT(rack_location,LEN(rack_location) - 1) AS INT);
Because (with your example data) the very first character is the only character that is not a number, the 'ORDER BY' just looks at all characters except for the first and then converts it to an integer, which will then sort as a number. Because the original column was character, just stripping of the first character would not work because it would still be treated as a varchar field and the result would be the same as you alreay have.
If the format is not always as you have given (for example - the may be 'aa123') then it becomes a little more involved.
Of course, more elegeant solutions will exist, but this is the one that came to me immediately.
Beaten to it 😉
Pretty much exactly the same solution!
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 1, 2012 at 8:23 am
That looks like you are using that column to hold two pieces of information (rack and BinNumber). It would make things a lot easier if you could separate those.
If you have use the cast approach in your where clause you are forcing a table scan. Of course if you don't have a lot of data it might be acceptable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply