Click here to Skip to main content
15,880,543 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi All,

I have one table in sql server 2008 R2, which is having values like

1.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0.0

Now when i apply sorting to this column then that time is giving me result like

1.0.0.0.0
10.0.0.0.0
11.0.0.0.0
12.0.0.0.0
2.0.0.0.0
3.0.0.0.0
4.0.0.0.0
5.0.0.0.0
6.0.0.0.0
7.0.0.0.0
8.0.0.0.0
9.0.0.0.0
which is wrong. as this column in varchar field, it is giving the output like above,
so how can proper sorted data.(any Conversion, Splitting text and then handle ?)


Thanks All
Posted
Comments
[no name] 20-Sep-13 3:41am    
can you add one more column in your table.
GauravThorat 23-Sep-13 1:59am    
Yes I can Add Column in table if it solve the issue..
gvprabu 20-Sep-13 6:18am    
One doubt, In your table contains "10.2.0.0.0" , "10.0.6.0.5"... Data also right. Then how u need to sort using this column?
GauravThorat 23-Sep-13 1:58am    
Yes, It may have data like 10.2.0.0.0 and 10.0.6.0.5, but it will be exist only if there is nodes before that like 10.1.0.0.0
and I need this as on proper sequence of this I am biding this to my tree view control

We can't use ABS() Directly on the Column Name as it cannot be converted to Decimal or int...
SQL
Select ColumnName From TableName Order by ABS(Left(ColumnName,CharIndex('.',ColumnName))) 
-- Abs will always Return positive value of any number
--or
Select ColumnName From TableName 
Order by Convert(Real,Left(ColumnName,CharIndex('.',ColumnName)))
 
Share this answer
 
v3
Comments
Maciej Los 20-Sep-13 4:05am    
+5
Raja Sekhar S 20-Sep-13 4:18am    
Thank you Maciej Los....
[no name] 20-Sep-13 4:21am    
++5
Raja Sekhar S 20-Sep-13 4:24am    
Thank you Rakesh...
gvprabu 20-Sep-13 5:24am    
nice example... my 5+
Solution 2 by Raja is very good. Another way is to split numbers into columns using CTE[^] and PIVOT[^]:
SQL
--declare temporary table
DECLARE @tmp TABLE (MyNumber VARCHAR(30))
--insert data
INSERT INTO @tmp (MyNumber)
SELECT '1.0.0.0.0'
UNION ALL SELECT '10.0.0.0.0'
UNION ALL SELECT '11.0.0.0.0'
UNION ALL SELECT '12.0.0.0.0'
UNION ALL SELECT '2.0.0.0.0'
UNION ALL SELECT '3.0.0.0.0'
UNION ALL SELECT '4.0.0.0.0'
UNION ALL SELECT '5.0.0.0.0'
UNION ALL SELECT '6.0.0.0.0'
UNION ALL SELECT '7.0.0.0.0'
UNION ALL SELECT '8.0.0.0.0'
UNION ALL SELECT '9.0.0.0.0'
--get the parts 
;WITH Parts AS
(
        --initial part (get first number)
	SELECT 1 AS PartNo, CONVERT(INT,LEFT(MyNumber, CHARINDEX('.',MyNumber)-1)) AS Number, RIGHT(MyNumber, LEN(MyNumber) - CHARINDEX('.',MyNumber)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX('.',MyNumber)>0
	UNION ALL
        --recursive part (get second, third... number)
	SELECT PartNo+1 AS PartNo, CONVERT(INT,LEFT(Remainder, CHARINDEX('.',Remainder)-1)) AS Number, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('.',Remainder)) AS Remainder
	FROM Parts
	WHERE CHARINDEX('.',Remainder)>0
	UNION ALL
	SELECT PartNo+1 AS PartNo, CONVERT(INT,Remainder) AS Number, NULL AS Remainder
	FROM Parts
	WHERE CHARINDEX('.',Remainder)=0
)
--define pivot fields
SELECT [RowNo], [1], [2], [3], [4], [5]
FROM (
        --source table
	SELECT ROW_NUMBER() OVER(PARTITION BY [PartNo] ORDER BY [PartNo]) AS RowNo, PartNo, Number
	FROM Parts
) AS DT
PIVOT(MAX([Number]) FOR [PartNo] IN ([1], [2], [3], [4], [5])) AS UNPVT
ORDER BY [1], [2], [3], [4], [5]


Output:
CSS
[RowNo] [1]     [2]     [2]     [4]     [5]
1	1	0	0	0	0
5	2	0	0	0	0
6	3	0	0	0	0
7	4	0	0	0	0
8	5	0	0	0	0
9	6	0	0	0	0
10	7	0	0	0	0
11	8	0	0	0	0
12	9	0	0	0	0
2	10	0	0	0	0
3	11	0	0	0	0
4	12	0	0	0	0
 
Share this answer
 
Comments
Raja Sekhar S 20-Sep-13 7:24am    
Nice one..+5!
Maciej Los 20-Sep-13 7:32am    
Thank you ;)
Try using ABS() on your values, see here : http://v3.thewatchmakerproject.com/journal/231/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900