[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1418
  • Last Modified:

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.
0
gerrystrat
Asked:
gerrystrat
  • 3
  • 3
  • 2
  • +4
1 Solution
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now