Sdílet prostřednictvím

TSQL - Solve it YOUR Way - Parsing Tips and Tricks - Returning Digits after the Decimal Point

  • Článek

Introduction:

As part of the blog series TSQL - Solve it YOUR Way, today we will tackle a numeric parsing question.  While past topics include solutions that vary from 15-150 lines of TSQL code, all of today's solutions will be very short.  A question was recently posted in the MSDN forums asking how to retrieve the digits after the decimal for a numeric data type.  Off the top of our heads, many people can probably come up with a few solutions.  After sending the question out to the blog series contributors, we came up with ten solutions that highlight creative parsing solutions and some less known tips and tricks within T-SQL. 

Topic: Given a numeric value, return the digits after the decimal (ex: 3.142 returns 142)

Solutions - provided by Tom Cooper, Olaf Helper, Kent Waldrop, Jingyang Li, & Steven Wang

 

Code Snippet

  1. -- Test data
  2. Declare @Test Table(Foo decimal(9,3));
  3. Insert @Test(Foo) Values (1.479), (2.000), (5.123);
  4.  
  5. -- Solution 1: ParseName
  6. Select Foo, ParseName(Foo, 1) From @Test;
  7.  
  8. -- Solution 2: Modulo / Absolute Value
  9. SELECT SUBSTRING(CONVERT(varchar(30), ABS(Foo) % 1), 3, 30) AS Result FROM @Test
  10.  
  11. -- Solution 3: Slightly modified version using modulo. Note: This solution returns '0' for input 2.000, whereas others return '000'
  12. select Foo, cast(sign(Foo) * 1000 * (Foo % 1) as integer
  13.   ) as decimal_Part
  14. from @test
  15.  
  16. -- Solution 4: Reverse / Floor solution
  17. SELECT REVERSE(CAST(FLOOR(REVERSE(ABS(Foo))) AS bigint)) from @Test
  18.  
  19. -- Solution 5: Use the original number minus an integer converted from the original number to an integer and then convert the decimal number to a varchar (50) to use the STUFF function to remove the '0.' part from the decimal number.
  20. SELECT Stuff(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), 1, 2, '') from @Test
  21.  
  22. -- Solution 6: Remove the "0." part from the above calculation with a substring function
  23. SELECT Substring(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), 3, Len(Foo)) from @Test
  24.  
  25. -- Solution 7: Use right function to remove the leading "0." from the string
  26. SELECT RIGHT(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50)), Len(Cast(Foo - Cast(Foo AS INT) AS VARCHAR(50))) - 2) from @Test
  27.  
  28. -- Solution 8: Find the . position using charindex and stuff everything before decimal
  29.   SELECT Stuff(Foo, 1, Charindex('.', Foo), '')from @Test
  30.  
  31. -- Solution 9
  32. SELECT Stuff(Foo, 1, Charindex(0X2E, Cast(Ltrim(Foo) AS VARBINARY(50)), 1), '') from @Test
  33.  
  34. -- Solution 10: CAST the string as an XML type and retrieve the value from the XML node
  35. SELECT Cast (N'<root><row>' + Replace(Foo, '.', '</row><row>') + '</row></root>' AS XML).value('(/root/row)[2]', 'nvarchar(50)') from @Test

 

Conclusion:

It is very common to encounter situations where parsing is required in some capacity.  The solutions presented above cover common parsing techniques such as SUBSTRING and PARSENAME, but also leverage other constructs such as STUFF, REVERSE, Modulo, and others in creative ways.  I hope that you are able to learn a lot by trying out the problem yourself and then reading through the additional solutions.

Special thanks to Tom, Olaf, Jingyang, Steven, and Kent for their valuable forums contribution and for contributing to this series!

Hope that helps,
Sam Lester (MSFT)

Comments

  • Anonymous
    October 23, 2012
    Truly amazing and amusing. A great example how T-sql can achieve same thing in so many different ways.

  • Anonymous
    October 24, 2012
    I just added one more that Jingyang had sent where he casts to xml and retrieves the value from the xml node.  

  • Anonymous
    October 24, 2012
    The comment has been removed

  • Anonymous
    October 24, 2012
    The comment has been removed

  • Anonymous
    October 25, 2012
    Interesting solutions - diversity in action. Four remarks:

  1. Solutions 5, 6 and 7 don't work with negative numbers...
  2. Hardcoded delimiters can be a source for bugs when the decimal symbol changes.
  3. Hardcoded numbers related to the number of decimals (aka scale) can lead to bugs as well
  4. Some of solutions stop working when the scale increases. For example the use of integer and bigint in 3rd, respectively 4th solution, make the solution stop working after 10, respectively 17 decimals. (Maybe the problem definition needs to be more clear.)

  • Anonymous
    October 27, 2012
    Amazing Solutions... Thanks Samuel for collating the solutions

  • Anonymous
    March 04, 2014
    How about: declare @n decimal(10,3) set @n=12.345 select @n as num ,@n-cast(abs(@n) as decimal) as method_1