INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

How to sort or compare multi-segment character information? by Walid
Posted: 7 Nov 05

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



Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close