sort strings containing numbers in specific format

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 2008 Forums

Transact-SQL (2008)

sort strings containing numbers in specific format

Author

Topic

adit
Starting Member

8 Posts

Posted - 2012-10-22 : 17:50:04

Hi I have a table with below values:
Column_Name: Value
Data: 10.22.340
12.34.55.346
12.34.55.321
12.33.56.121
27

I need to sort them as below:
10.22.340
12.33.56.121
12.34.55.321
12.34.55.346
27

Any ideas on how this can be done? Appreciate help.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-22 : 18:56:22

Are they always going to be numbers? And are they going to have a MAX of 4 parts? If so, this might work for you:

DECLARE @T TABLE (Value VARCHAR(50))
INSERT @T VALUES

('10.22.340'),
('12.34.55.346'),
('12.34.55.321'),
('12.33.56.121'),
('27')


SELECT 
    Value
FROM 
    @T
ORDER BY
    CAST(PARSENAME(Value + REPLICATE('.0', 3 - (LEN(Value) - LEN(RTRIM(REPLACE(Value, '.', ''))))), 4) AS INT),
    CAST(PARSENAME(Value + REPLICATE('.0', 3 - (LEN(Value) - LEN(RTRIM(REPLACE(Value, '.', ''))))), 3) AS INT),
    CAST(PARSENAME(Value + REPLICATE('.0', 3 - (LEN(Value) - LEN(RTRIM(REPLACE(Value, '.', ''))))), 2) AS INT),
    CAST(PARSENAME(Value + REPLICATE('.0', 3 - (LEN(Value) - LEN(RTRIM(REPLACE(Value, '.', ''))))), 1) AS INT)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-23 : 11:55:00

The natural sort works for the data set you posted.....


DECLARE @T TABLE (Value VARCHAR(50))
INSERT @T VALUES  ('10.22.340')
				, ('12.34.55.346')
				, ('12.34.55.321')
				, ('12.33.56.121')
				, ('27')
				
SELECT * FROM @T ORDER BY [value]

So can you post some data that breaks what you want? Thanks.
Results:


10.22.340
12.33.56.121
12.34.55.321
12.34.55.346
27

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

adit
Starting Member

8 Posts

Posted - 2012-10-23 : 14:22:43

Hi Lamprey,

Thanks for your reply. The numbers could have maximum of 9 parts as below:
19.45.2.80.7.543.211.765.18760369
19.45.2.80.7.544.211.765.18760369
19.45.2.80.7.544.211.765.9870762

Please suggest here. thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 15:37:13

I stared at this for a while too, and like Charlie, I can't figure out why the natural string sort wouldn't work for you, at least based on the examples that you have posted.

adit
Starting Member

8 Posts

Posted - 2012-10-24 : 01:32:42

Yes, that doesn't work for me because i have values like '4.5.345','10.3.42.57.29.31.46' along with above.
Appreciate your response and help. Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-24 : 04:36:00

OK -- here is a way. Though it's pretty ugly:


USE tempdb
GO

/* udfSplit (A Fast String Splitter) **************************************************************
 *
 * Uses a number table to *very* quickly split the text (@text). Splits on the delimiter (@d)
 * Returns Table of ( [RowID], [SplitText] ). Inlineable for CROSS APPLY etc.
 *
 * Charlie
 *
 *************************************************************************************************/
CREATE FUNCTION [dbo].[udfSplit] (@text NVARCHAR(4000), @d NVARCHAR(50))
RETURNS TABLE AS RETURN (
WITH numbers(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.[n])
  FROM
	         ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  a ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  b ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  c ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) AS  d ([n])
  )
SELECT
	  [RowID] = ROW_NUMBER() OVER ( ORDER BY [n] ASC )
	, [SplitText] = SUBSTRING(
		@d + @text + @d
		, [n] + LEN(@d)
		, CHARINDEX(@d, @d + @text + @d, [n] + LEN(@d)) - [n] - LEN(@d)
		)
FROM numbers AS n
WHERE [n] <= LEN(@d + @text + @d) - LEN(@d)
  AND SUBSTRING(@d + @text + @d, [n], LEN(@d)) = @d
)
GO

DECLARE @T TABLE (Value VARCHAR(50))
INSERT @T VALUES  ('10.22.340')
				, ('12.34.55.346')
				, ('12.34.55.321')
				, ('12.33.56.121')
				, ('27')
				, ('4.5.345')
				, ('10.3.42.57.29.31.46')
				
SELECT
	[value]
	, [padded] AS [sortOnValue]
FROM
	@t AS t
	CROSS APPLY (
		SELECT RIGHT('000000' + [SplitText], 6) + '.'
		FROM dbo.udfSplit(t.[Value], '.') AS v
		ORDER BY v.[RowID]
		FOR XML PATH('')
		)
		AS c ([padded])
ORDER BY
	c.[padded]
	

GO

DROP FUNCTION [dbo].[udfSplit]
GO

It uses a string splitting function to split on the period (.) then uses an XML trick to rebuild the string padding each entry with zeros

Finally it sorts on the padded value

Resuts:


(7 row(s) affected)
value                                              sortOnValue
-------------------------------------------------- ---------------------------------------------------
4.5.345                                            000004.000005.000345.
10.3.42.57.29.31.46                                000010.000003.000042.000057.000029.000031.000046.
10.22.340                                          000010.000022.000340.
12.33.56.121                                       000012.000033.000056.000121.
12.34.55.321                                       000012.000034.000055.000321.
12.34.55.346                                       000012.000034.000055.000346.
27                                                 000027.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources