Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to sort or compare multi-segment character information?

T-SQL Hints and Tips

How to sort or compare multi-segment character information?

by  Walid  Posted    (Edited  )
The problem of comparing or sorting multi-segment information, like version numbers for example, faced me so often so I decided to make a generic function for it.

The function will allow you to sort a sequence like this

1.7.1.3 01
12.15.1 01.01
1.5.3.2 01.05.03.02
1.1 01.07.01.03
1 INTO ==> 05.01
5.1 05.01.03
5.10.3 05.10.03
5.1.3 12.15.01

by executing this simple select statement
SELECT dbo.PadLEx(Field_Name , 3, 2, '.', '0') FROM Table_Name ORDER BY Field_Name

The function will also help you compare segmented quantities that will normally results in false answer such as

IF '10.1.3' > '2.1.2'
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

The result is FALSE

If this is version #, it is clear that 10.1.3 is greater than '2.1.2' but because the compression was made based on the ASCII code, the result was false.

If you run the same query as
IF dbo.PadLEx('10.1.3' , 3, 2, '.', '0') > dbo.PadLEx('2.1.2' , 3, 2, '.', '0')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
It will give you the expected result, TRUE, this time.

I decided to share this function with this community hoping that it will make some oneÆs programming task easier.

Thanks all and here is the function

Code: 
CREATE FUNCTION dbo.PadLEx(@CharExp VARCHAR(255), @NumberOfSegments TINYINT , @FinalLengthOfSegment TINYINT, @Delimiter CHAR(1), @PadChar CHAR(1))
RETURNS VARCHAR(255)
AS
BEGIN
	-- @NumberOfSegments is the number of segment you wish to pad. The function will allow you to pad only 3 segments of 4 segments info
	-- For example SELECT dbo.PadLEx('1.2.3.4', 3, 2, '.', '0') ==> 01.02.03.4 (We may need it)
	-- @FinalLengthOfSegment, how many char you want each segment to have 
DECLARE @Result VARCHAR(255)
SET @Result = ''

IF @NumberOfSegments = 0
-- Don't work on the rest of these segments
BEGIN
	RETURN @CharExp
END

IF @CharExp IS NULL OR RTRIM(LTRIM(@CharExp)) = ''
BEGIN
	RETURN REPLICATE(@PadChar, @FinalLengthOfSegment)	
END
ELSE
BEGIN
	IF CHARINDEX(@Delimiter , @CharExp) = 0
	BEGIN
		SET @Result = REPLICATE(@PadChar, @FinalLengthOfSegment - DATALENGTH(@CharExp)) + @CharExp
		RETURN @Result
	END
	ELSE
	BEGIN
		DECLARE @nStartPosition TINYINT , @EndPosition TINYINT , @TempExp VARCHAR(10)
		SET @Result	    = ''	
		SET @nStartPosition = 1
		SET @EndPosition    = CHARINDEX(@Delimiter , @CharExp)
		SET @TempExp        = SUBSTRING(@CharExp, @nStartPosition, @EndPosition)
		SET @Result = @Result + REPLICATE(@PadChar, @FinalLengthOfSegment - (DATALENGTH(@TempExp) - 1)) + @TempExp
                -- Recursive call	
		SET @Result = @Result + dbo.PadLEx(SUBSTRING(@CharExp, @EndPosition + 1, 
			CASE WHEN DATALENGTH(@CharExp) - @EndPosition <= 0 THEN 0 ELSE DATALENGTH(@CharExp) - @EndPosition END) ,
			@NumberOfSegments -1 , @FinalLengthOfSegment , @Delimiter , @PadChar)
		
	END
END
RETURN @Result
END -- End Of Function


Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top