Sort like Access's Val() function in MS SQL Server

I have a set of data that includes the following:

C1000
C1002
1
10
11
12A
2


When I sort the data, I would like it to appear like this:
1
2
10
11
12A
C1000
C1002

It was easy to do in Access with the Val() function, but I am having trouble with this in MS SQL.  Anyone have any suggestions?

Thank you.
gerrystratAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
SELECT fieldname FROM table1 ORDER BY fieldname
0
DabasCommented:
Hi ShogunWade:
That would not work. The field is not numeric, hence 10 will appear before 2

I am not so sure that Val works properly in Access either. What Val does is to look at the expression from left to right and stop the moment it is not numeric.
Hence Val("12A") = 12, Val("C1000") = 0, Val("C1002")=0 Val("XYZ") = 0. You have no guarantee that XYZ will actually appear after C1002

Dabas
0
HilaireCommented:
Here's an equivalent for the val function.
Using it in a query won't be very efficient, but it might help if you don't have too much rows

create function ufn_val(@strIn varchar(200))
returns int
as
begin
return case patindex('%[^0-9]%', @strIn)
            when 0 then cast(left(@strIn, 10) as int)
            when 1 then 0
            else cast(left(left(@strIn, patindex('%[^0-9]%', @strIn) - 1), 10) as int)
      end
end
go

-- to use it
SELECT fieldname FROM table1 ORDER BY dbo.ufn_val(fieldname)

as the function returns int datatype, it keeps only ten digits to avoid conversion overflow


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ShogunWadeCommented:
good point dabas.   Slap my wrists for forgetting my ASCII table ;)
0
izblankCommented:
You can sort by two fields, like that:

ORDER BY ufn_val(myField), myField

This will guarantee that 12B always come after 12A, etc
0
izblankCommented:
>>This will guarantee that 12B always come after 12A, etc

Of course, C12 will still come before C2, but I hope that's a minor inconvenience :-)
0
SashPCommented:
Hi gerrystrat

Perhaps it is much easier than it appears the problem with sorting numbers in a character field is the position of the units, ten, hundreds etc.

So align the numbers correctly and the sort is simple.

ie

select *
FROM [Table Name]
ORDER BY
 RIGHT('          ' + rtrim(ltrim([Column Name])),10)

Cheers Sash
0
SashPCommented:
gerrystrat,

Please ignore last post doesn't work.
0
ala_frostyCommented:
Assuming that 'C' is the first and only character that may appear in the otherwise completely integer string:

SELECT fieldname
FROM table1
order by
case when
     left(fieldname,1)='C'
then
     '9' + replicate('0',15-log10(cast(substring(fieldname,2,99) as int)))
else
     replicate('0',16-log10(cast(fieldname as int)))
end
0
ala_frostyCommented:
I picked 16 as the number of digits completely arbitrarily. Feel free to adjust as needed.
0
DabasCommented:
ala_frosty:
The question mentions 12A...

Dabas
0
gerrystratAuthor Commented:
I attempted to use Hilaire's suggestion.  Unfortunately, it displays like this.

C1000
C1002
1
2
10
11
12A


Is there a method to display the fields that begin with C to show up last in the display order, so it can appear like this:

1
2
10
11
12A
C1000
C1002
0
ShogunWadeCommented:
?  I notice from your sample data there are not X Y Z etc in there.    I dont suppose these are HEX numbers are they ?
0
HilaireCommented:
OK New version

drop function ufn_val
go
create function ufn_val(@strIn varchar(200))
returns int
as
begin
return case patindex('%[^0-9]%', @strIn)
          when 0 then cast(left(@strIn, 10) as int)
          when 1 then 2147483647  -- biggest int value possible
          else cast(left(left(@strIn, patindex('%[^0-9]%', @strIn) - 1), 10) as int)
     end
end
go





0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gerrystratAuthor Commented:
Hillaire,

Thank you for your help.  I had to add one small tweak when I do the sort in order to get the list to display 100% correctly.

SELECT fieldname
FROM table1
ORDER BY dbo.ufn_val(fieldname), fieldname

You get the points.
0
ala_frostyCommented:
for that matter

SELECT fieldname
FROM table1
ORDER BY case patindex('%[^0-9]%', @strIn) when 0 then cast(left(@strIn,38) as decimal(38,0))
else cast(replicate('9',38) as decimal(38,0) ) end , fieldname

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.