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