• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

is there a way to sort fractions in sql?

I have a series of values like this:

14 1/2
14 1/4
14 5/16
15 15/16
15 7/8

and I need to be able to sort using those values.
0
jbrahy
Asked:
jbrahy
  • 3
  • 2
  • 2
2 Solutions
 
YiogiCommented:
Do you mean you have strings like those??? If so you'd have to convert them to numbers to be able to sort them.
0
 
appariCommented:
no direct way,
you may write a function to evaluate the fractions to decimal equivalent and use that value to sort your results.
0
 
jbrahyAuthor Commented:
ok, that's what I thought but I'm going to leave this question open for a bit to see if someone has another idea.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
YiogiCommented:
It's not really about ideas. It simply cannot be done. Converting it to a number is the only way you can sort it as a number.
0
 
appariCommented:
created a quick sample function , not tested for all conditions, you can make a start from here

create the function using the code snippet and use it in your sql as follows

select dbo.ConvertFract(yourFieldName), yourFieldName from tablename order by 1
CREATE FUNCTION ConvertFract
(
	@Val varchar(10)
)
RETURNS numeric(20,10)
AS
BEGIN
	declare @temp varchar(10)
	declare @ret numeric(20,10)
	declare @frac numeric(20,10)
 
	select @temp = ltrim(rtrim(@val)),@frac=0
 
	if charindex(' ', @temp)<> 0
	begin
		select @ret = convert(numeric(20,10),SUBSTRING(@temp,1,charindex(' ', @temp)-1))
		Select @temp = replace(@temp,SUBSTRING(@temp,1,charindex(' ', @temp)),'')
	end
 
	if charindex('/', @val)<> 0
	begin
		select @frac = convert(numeric(20,10),SUBSTRING(@temp,1,charindex('/', @temp)-1))
		Select @temp = replace(@temp,SUBSTRING(@temp,1,charindex('/', @temp)),'')
		select @frac = @frac / convert(numeric(20,10),@temp)
	end
 
	RETURN @ret + @frac
 
END

Open in new window

0
 
appariCommented:
small modification try this,
Alter FUNCTION ConvertFract
(
	@Val varchar(10)
)
RETURNS numeric(20,10)
AS
BEGIN
	declare @temp varchar(10)
	declare @ret numeric(20,10)
	declare @frac numeric(20,10)
 
	select @temp = ltrim(rtrim(@val)),@frac=0, @ret = 0
 
	if charindex(' ', @temp)= 0 and charindex('/', @temp)= 0
		select @ret = convert(numeric(20,10),@temp)
 
	if charindex(' ', @temp)<> 0
	begin
		select @ret = convert(numeric(20,10),SUBSTRING(@temp,1,charindex(' ', @temp)-1))
		Select @temp = replace(@temp,SUBSTRING(@temp,1,charindex(' ', @temp)),'')
	end
 
	if charindex('/', @val)<> 0
	begin
		select @frac = convert(numeric(20,10),SUBSTRING(@temp,1,charindex('/', @temp)-1))
		Select @temp = replace(@temp,SUBSTRING(@temp,1,charindex('/', @temp)),'')
		select @frac = @frac / convert(numeric(20,10),@temp)
	end
 
	RETURN @ret + @frac
 
END

Open in new window

0
 
jbrahyAuthor Commented:
I had to split it between the two of your because I think you answered it within seconds of each other. thanks for you help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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