0

I have a nvarchar(500) column in SQL server 2008 that contains letters and numbersand here is what data looks like when I user ORDER BY clause in SQL Server...

env
guide
Seg 18 - NWS
Seg 19  - NWS
Seg 1A - ECC
Seg 1B - ECC
Seg 22 - xxx
Seg 23 - GL
Seg 3- GL
Seg 4 - GL
Utils

But I would like to get this result...

env
guide
Seg 1A - ECC
Seg 1B - ECC
Seg 3- GL
Seg 4 - GL
Seg 18 - NWS
Seg 19  - NWS
Seg 22 - xxx
Seg 23 - GL
Utils

Any suggestions?

Jesuraja
  • 3,774
  • 4
  • 24
  • 48
dev123
  • 1
  • Are you guaranteed that the number you want to sort by is **always** preceded by `Seg `? – PM 77-1 Jul 11 '14 at 21:23
  • No, its not always Seg, it can be any text. – dev123 Jul 11 '14 at 21:25
  • 3
    You could probably write some really hacky SQL query to extract the number after `Seg ` and then sort by that too but why not just do it the right way and store that int/string in another column and sort by the additional column? – Mark Silverberg Jul 11 '14 at 21:25
  • Then please provide real-life example with the desired outcome. Also indicate if there is any pattern your data always follow. – PM 77-1 Jul 11 '14 at 21:32
  • What about the suffix to the data? Is that always going to be characters? In other words, the only numeric values in the column are what you want to sort by? – Sean Lange Jul 11 '14 at 21:39

3 Answers3

0

This is called a natural sort and going to be a nightmare for you if you don't own the database. You can follow the response here which seems quite robust, but involves injecting a new function into the CLR.

As one of the commenters suggests you can split and then sort on the 3 columns, but if your text isn't a fixed width, you may run into some more problems and result in an ultimate hacky solution. This is a decent T-SQL solution you can try but it relies on fixed width... to which more people suggest padding your numbers.

What else do you want? Have you tried and found those wanting?

Community
  • 1
  • 1
TheNorthWes
  • 2,661
  • 19
  • 35
0

First, I assume you have only one number in your pattern. If not, you can extend the below code assuming you have some known rule for detecting the right string.

So, this code here below (which I don't have any machine to test on currently...) finds the number start index and length, extracts it and converts it into an integer (I assume the string is inside a variable named @data):

DECLARE @numindex int;
SELECT @numindex = PATINDEX('[0-9]', @data);
DECLARE @numlength int;
SELECT @numlength = PATINDEX('[^0-9]', SUBSTRING(@data, @numindex, LEN(DATA) - @numindex - 1));
-- This is the result below
SELECT CONVERT(int, SUBSTRING(@data, @numindex, @numlength))

If all the assumptions I wrote do suit you, you could either create a scalar valued function from this, or add this directly to the query (which may make the query a bit unreadable...).

Regarding performance, this is obviously not ideal to sort like this on every query. If this is going to happen a lot and the data isn't going to change frequently, maybe creating a view that would possibly be cached would improve the performance.

Barak Itkin
  • 4,872
  • 1
  • 22
  • 29
-2

Here is one way to do this assuming the only numeric values are what you posted. If there is the possibility that the suffix can also contain numbers this will need a slight tweak. I am using a super awesome inline table value function created by Dwain Camps at sql server central. I know this site requires a login but it is free and this technique is well worth signing up for.

http://www.sqlservercentral.com/articles/String+Manipulation/94365/

Using his function this is pretty simple. This is 100% set based. No loops, while or cursors at all.

declare @Table table (SomeValue varchar(25))

insert @Table
select 'Seg 1A - ECC' union all
select 'Seg 1B - ECC' union all
select 'Seg 3- GL' union all
select 'Seg 4 - GL' union all
select 'Seg 18 - NWS' union all
select 'Seg 19  - NWS' union all
select 'Seg 22 - xxx' union all
select 'Seg 23 - GL'

select t.*
from @Table t
outer apply dbo.PatternSplitCM(t.SomeValue, '[%0-9%]') x
where x.Matched = 1
order by x.Matched desc, Item
Sean Lange
  • 33,028
  • 3
  • 25
  • 40