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@.stargate.net
http://www.sugapablo.com | ICQ: 902845If 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(SUBSTRING(REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1,
CHARINDEX('-', REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1) -
CHARINDEX('-', REVERSE(col)) - 1)),
REVERSE(SUBSTRING(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(REPLACE(col, '-', '-'), 3),
PARSENAME(REPLACE(col, '-', '-'), 2),
PARSENAME(REPLACE(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 )
