CODEWITHSUNDEEP
sqldata-manipulationdata-cleaning
wickedflow
wickedflow

Reputation: 85

Retrieving number between zeros and a character

I have a list of numbers that need to be cleaned, and the code I'm using doesn't seem to be working.

An example of the number pre-manipulation: 0000000001C How the number is supposed to be: 0.13 An example of the number pre-manipulation: 0000000173A How the number is supposed to be: 17.31

The code I've been working on is a bit confusing: 

select amount, 
       case when right(amount,1) = 'A' then concat(right(amount, charindex('0', reverse(amount))-1), replace(amount,'A','1'))
            when right(amount,1) = 'C' then concat(right(amount, charindex('3', reverse(amount))-1), replace(amount,'C','3')) end
from db

The numbers I could get are 1C00000000013, 173A00000001731

Any help is appreciated!

Upvotes: 0

Views: 50

Answers (1)

Julian
Julian

Reputation: 4085

Not exactly sure what the structure of your numbers are but I make the assumption that only the last character in your list is not a digit and it can only be "A' or 'C'

WITH amounts AS(
  SELECT '0000000001C' amount
   UNION
  SELECT '0000000173A' amount
)
SELECT 
  case right(amount,1)
    when 'A' then replace(right(amount, charindex('0', reverse(amount))-1), 'A', '1')
    when 'C' then replace(right(amount, charindex('0', reverse(amount))-1), 'C', '3')
  end AS amount
FROM amounts;

Will give you '13' and '1731' which you can convert in numbers based on the amount of decimals.

You were very close with your SELECT yourself.

Upvotes: 1

Related Questions