T-SQL Sorting Challenge

You are given a table called t1 with a character string column called val. Each string in the val column holds a dot separated list of integers. Your task is to write a T-SQL solution that sorts the strings based on the integer values constituting the string segments. Note that the number of integers in each string may vary, and is only limited by the column type – VARCHAR(500).

Use the following code to create the table t1 and populate it with sample data:

set nocount on;

use tempdb;

if object_id('dbo.t1') is not null drop table dbo.t1;

create table dbo.t1

(

  id int not null identity primary key,

  val varchar(500) not null -- guaranteed to have integers seperated by dots

);

go

insert into dbo.t1(val) values('100');

insert into dbo.t1(val) values('7.4.250');

insert into dbo.t1(val) values('22.40.5.60.4.100.300.478.19710212');

insert into dbo.t1(val) values('22.40.5.60.4.99.300.478.19710212');

insert into dbo.t1(val) values('22.40.5.60.4.99.300.478.9999999');

insert into dbo.t1(val) values('10.30.40.50.20.30.40');

insert into dbo.t1(val) values('7.4.250');

Here’s the desired output:

id          val

----------- ----------------------------------

7           7.4.250

2           7.4.250

6           10.30.40.50.20.30.40

5           22.40.5.60.4.99.300.478.9999999

4           22.40.5.60.4.99.300.478.19710212

3           22.40.5.60.4.100.300.478.19710212

1           100

Extra points if your solution will also support negative integers. Add the following sample data to test negatives:

-- Add negative values

insert into dbo.t1(val) values('-1');

insert into dbo.t1(val) values('-2');

insert into dbo.t1(val) values('-11');

insert into dbo.t1(val) values('-22');

insert into dbo.t1(val) values('-123');

insert into dbo.t1(val) values('-321');

insert into dbo.t1(val) values('22.40.5.60.4.-100.300.478.19710212');

insert into dbo.t1(val) values('22.40.5.60.4.-99.300.478.19710212');

Desired output including negative values:

id          val

----------- -----------------------------------

13          -321

12          -123

11          -22

10          -11

9           -2

8           -1

7           7.4.250

2           7.4.250

6           10.30.40.50.20.30.40

14          22.40.5.60.4.-100.300.478.19710212

15          22.40.5.60.4.-99.300.478.19710212

5           22.40.5.60.4.99.300.478.9999999

4           22.40.5.60.4.99.300.478.19710212

3           22.40.5.60.4.100.300.478.19710212

1           100

Cheers,

BG