Jump to content

Downloads need sort by price option

Recommended Posts

  • 3 weeks later...

Trying to get someone started

I have an idea.

Looking at how prices are stored in a long string, seems the "plain" price is always the 3rd index in that file cost array.

Couldn't the query be written to sort on just that value using some string logic?

It would be an ugly query but I think it could work. 

start position use Use CHARINDEX(substring, string, start)  to find 'amount'

end position use CHARINDEX(substring, string, start)  to find 'currency'

then you have the start and end position and you can grab the amount and sort on it using SUBSTRING(string, start, length)

Something like as follows.

select (substring(file_cost,charindex(file_cost,'amount":"',1),(charindex(file_cost,'","currency',1)-charindex(file_cost,'amount":"',1))) as plain_price
where validatepermissions = true
sort desc on plain_price

That query won't work but I think it gives someone the idea. I think with some adjustments this could work much easier and allow sorting on the fly with no extra columns or table conversions etc.

Link to comment
  • 1 year later...
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...