How to retrieve the digits before or after the decimal for a numeric data type





--how to retrieve the digits before the decimal for a numeric data type
Declare @d decimal(20,6)=123456.712345

Declare @d decimal(20,6)=123456.712345
 
select 1+FLOOR(LOG10(@d)), left( @d, 1+FLOOR(LOG10(@d)))
SELECT len(cast(@d as int)) , left( @d, len(cast(@d as int)))
Select Charindex(0X2E,Cast(Ltrim(@d) as VARBINARY(32)))-1 ,left( @d, Charindex(0X2E,Cast(Ltrim(@d) as VARBINARY(32)))-1)
select Len(parsename(cast(@d as varchar(32)),2)) , left( @d, Len(parsename(cast(@d as varchar(32)),2)))
Select len(FLOOR(@d)), left( @d, len(FLOOR(@d)))

--https://social.msdn.microsoft.com/Forums/en-US/1d1ca264-9118-420e-a8d1-abea46dd7f18/how-to-count-the-number-of-digits-before-decimal-value?forum=transactsql


---how to retrieve the digits after the decimal for a numeric data type

Select @d, ParseName(@d, 1) 
SELECT @d, SUBSTRING(CONVERT(varchar(30), ABS(@d) % 1), 3, 30) 
SELECT @d, REVERSE(CAST(FLOOR(REVERSE(ABS(@d))) AS bigint))
select @d, cast(sign(@d) * 1000000 * (@d % 1) as int)
SELECT @d,Stuff(Cast(@d - Cast(@d AS INT) AS VARCHAR(50)), 1, 2, '')
SELECT @d, Stuff( @d, 1, Charindex('.',  @d), '')
SELECT @d, Stuff(@d, 1, Charindex(0X2E, Cast(Ltrim(@d) AS VARBINARY(50)), 1), '')
SELECT @d, Cast (N'<root><row>' + Replace(@d, '.', '</row><row>') + '</row></root>' AS XML).value('(/root/row)[2]', 'nvarchar(50)')
SELECT @d,RIGHT(Cast(@d - Cast(@d AS INT) AS VARCHAR(50)), Len(Cast(@d - Cast(@d AS INT) AS VARCHAR(50))) - 2) 
SELECT @d, Substring(Cast(@d - Cast(@d AS INT) AS VARCHAR(50)), 3, Len(@d))

--http://blogs.msdn.com/b/samlester/archive/2012/10/23/tsql-solve-it-your-way-parsing-tips-and-tricks-returning-digits-after-the-decimal-point.aspx



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s