I have prices coming in my source file like below -

78-22¼, 78-18⅝

I need to calculate these price. For example for first case result should be 78-22.25. I searched a lot but found that SQL supports few of these characters only. Is there anyway to make sure we are able to calculate for whatever value we are getting. Solution in either SQL or PowerShell could work.

CodePudding user response：

You could write a PowerShell function to convert the fractions to decimals:

PS ~> ConvertTo-Decimal '18⅝'
18.625

To do so, we'll need to write a function that:

• Uses regex to identify and extract the integer and fraction parts
• Uses [char]::GetNumericValue() to get the decimal representation of the fraction
• Outputs the sum of the two

function ConvertTo-Decimal {
  param(
    [Parameter(Mandatory)]
    [string]$InputObject
  )

  if($InputObject -match '^(-?)(\d )(\p{No})?$'){
    $baseValue =  $Matches[2]
    if($Matches[3]){
      $baseValue  = [char]::GetNumericValue($Matches[3])
    }
    if($Matches[1] -eq '-'){
      $baseValue *= -1
    }

    return $baseValue
  }

  return 0
}

CodePudding user response：

In T-SQL you could write a function like this that takes a vulgar fraction and replaces it with its decimal equivalent (this is not completely exhaustive, but handles the most common fractions, and makes a guess about whether you want .666 or .667 or something else for ⅔):

CREATE FUNCTION dbo.FractionToDecimal(@str nvarchar(255))
RETURNS TABLE
AS
  RETURN 
  (
    SELECT str = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
         REPLACE(REPLACE(REPLACE(REPLACE(@str, N'¼','.25'),
     N'½','.5'),   N'¾','.75'),  N'⅓','.333'), N'⅔','.666'),
     N'⅛','.125'), N'⅜','.375'), N'⅝','.625'), N'⅞','.875')
  );

Sample usage:

DECLARE @x table(str nvarchar(255));
INSERT @x VALUES(N'78-22¼'),(N'78-18⅝');

SELECT [input] = i.str, [output] = o.str
   FROM @x AS i
  CROSS APPLY dbo.FractionToDecimal(str) AS o;

Output:

Working example in this fiddle.