0

I have an amount column which is in money format. I have tried using parsename, converting it to varchar to use substring function but unable to extract exact values appearing after decimal. Attaching the screenshot for reference.

select  home_currency_amount, 
    cast(home_currency_amount as varchar(50)) as amt_varchar,
    parsename(home_currency_amount, 1) as amt_prsnm
from #temptbl;
---Below is the output:
home_currency_amount    amt_varchar amt_prsnm
39396.855               39396.86    86
1112.465                1112.47     47
5635.1824               5635.18     18

E.g. if value is 39396.855, desired output would be 855.

Thanks in advance for the help.

Prash
  • 59
  • 3
  • 1
    `select ParseName(Str(,18,4),1)` – Stu Jan 19 '23 at 10:52
  • FYI, if you *are* using `money` I recommend against it. Use a `decimal` data type. Probably a `decimal(18,4)` would suffice. – Thom A Jan 19 '23 at 10:55
  • Does this answer your question? [SQL - How do I get only the numbers after the decimal?](https://stackoverflow.com/questions/3418606/sql-how-do-i-get-only-the-numbers-after-the-decimal) – Skin Jan 19 '23 at 11:02
  • 1
    Hi @Stu - I am getting the desired output with your code. Thanks for the help! – Prash Jan 19 '23 at 11:21
  • I downvoted the question because the OP said that the datatype was money. That means that their statement of "E.g. if value is 39396.855, desired output would be 855." is incorrect. Money will return 39396.8550, which is what Stu's code returned (8550) and the OP said above was "the desired output". – Jeff Moden Feb 12 '23 at 23:43

1 Answers1

1

First perform the mod operation on value with 1 to get the decimal part. We will be getting as '0.decimal_part' as we require only decimal part without '0.' so we are replacing it and finally casting as integer. Hope it helps in your case..

select cast(replace(cast( 1.23 % 1 as varchar),'0.','') as int)

enter image description here

Lasya
  • 11
  • 4
  • Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Feb 10 '23 at 21:54
  • @JeremyCaney - Why is this any worse than what Stu posted in the comments? – Jeff Moden Feb 12 '23 at 23:09
  • @Lasya - I down voted this because it's subject to rounding, especially when there are more than 2 digits to the right of the decimal as the OP pointed out. – Jeff Moden Feb 12 '23 at 23:11
  • @JeffModen - Even if there are more than 2 digits to the right of decimal the code works fine as we are taking the mod( value , 1) which returns the decimal part the value as 0.decmial_part, which we are replacing the '0.' part casting as integer. – Lasya Feb 14 '23 at 05:30
  • @JeffModen - You can see the screenshot attached where it is giving the expected value even with more than 2 digits in decimal part. Hope you got it. – Lasya Feb 14 '23 at 05:42
  • I voted it down before I saw that what the op said they wanted wasn't what they actually wanted. I undid my down-vote. – Jeff Moden Feb 14 '23 at 20:04
  • Ah... actually, it does round and I forgot the reason. It's the bloody MONEY datatype. Your code rounds if the MONEY datatype contains the value. Try it. Here's the code I tested with. DECLARE @ Money MONEY = 39396.855; select cast(replace(cast( @ Money % 1 as varchar),'0.','') as int); --(Remove the space after the "@" to make it work. I had to add the space because the forum was squawking at me about referencing more than 1 user.) You should fix the code to work with the datatype that OP said he was using. – Jeff Moden Feb 14 '23 at 20:27