[thelist] sorting question

[Paul Cowan]

JCanfield at magisnetworks.com wrote:
> Is there a way to parse, sort, and concatenate within SQL?
> Any other ideas?

I will assume that you can't change the DB format to something like
	AccountNumberMajor
	AccountNumberMinor
(or whatever) and then
	ORDER BY AccountNumberMajor, AccountNumberMinor

In that case, you could try (apologies for wrapping here, and
non-my-employer's-coding-standards-compliant typography):

	select
		AccNo,
		convert(int,left(
				convert(varchar, AccNo),
				patindex('%.%', convert(varchar, AccNo)) -
1)
			  ),
		convert(int,right(
				convert(varchar, AccNo),
				len(convert(varchar, AccNo))
					- patindex('%.%', convert(varchar,
number)))
			  )
	from
		Accounts
	order by
		2, 3

Which is nasty, but will work as long as every number follows the
format xx.yy - everything has one and only one decimal point.
Effectively, you are converting it to a string, finding the
decimal point, splitting it in two and then converting those
back to INTs.

If you have any whole numbers in there (like '1'), it will
need some corresponding tweaking - look at using the STR function
or something similar to make sure there's a decimal there. Using
views to do the splitting and converting will also simplify this
quite a lot.

Hope this helps,

Paul.