ORDER BY question: splitting string into 2 orders?

[Sugapablo]

I have a column named "LIST" in a table with strings like the following:

151231-1002-02-1001
151231-1001-02-1001
151231-1002-02-1002
151231-1003-02-1001
etc....

What I'd like to do is include an ORDER BY statement that splits the
string, so that the order would be by the second set of four numbers
(i.e. between the first and second - marks), followed by the third set
of two numbers, and then by the last set of four numbers.

How would I do something like this?

--
Sugapablo - russpghREMOVE@s targate.net
http://www.sugapablo.com | ICQ: 902845

[Anith Sen]

Re: ORDER BY question: splitting string into 2 orders?

If this is a fixed width column with fixed formats, you can use substring to
parse the value like:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col, 8, 4),
SUBSTRING(col, 13, 2),
RIGHT(col, 4) ;

If these are variable length formatted, then you have more work to do:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col,
CHARINDEX('-', col) + 1,
CHARINDEX('-', col,
CHARINDEX('-', col) + 1) -
CHARINDEX('-', col) - 1),
REVERSE(SUBSTRI NG(REVERSE(col) ,
CHARINDEX('-', REVERSE(col)) + 1,
CHARINDEX('-', REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1) -
CHARINDEX('-', REVERSE(col)) - 1)),
REVERSE(SUBSTRI NG(REVERSE(col) , 1,
CHARINDEX('-', REVERSE(col)) - 1)) ;

Another trick is to use PARSENAME function. Note that the return expression
for PARSENAME function is unicode though. See SQL Server Books Online for
more details.

SELECT *
FROM tbl
ORDER BY PARSENAME(REPLA CE(col, '-', '-'), 3),
PARSENAME(REPLA CE(col, '-', '-'), 2),
PARSENAME(REPLA CE(col, '-', '-'), 1) ;

If each of these portions are of business significance, why are you
representing them as a single column? If consolidation is needed for certain
specific requirements, you can use a view for such representation.

--
- Anith
( Please reply to newsgroups only )