How to Get the Digits After the Decimal Point

I have spent this Sunday afternoon to work on solutions for this simple request.
I found about ten ways to get the result before dinner. I keep thinking to get another two at the end of this post.
-Topic #9
–how to get the digits after the decimal point (ex: 3.589 returns 589, etc).

DECLARE @d DECIMAL(10, 3)=10.589

–Cast the number to a varchar(50) string
DECLARE @s VARCHAR(50)=Cast(@d AS VARCHAR(50))

SELECT

/*
Solution 1: Use the original number minus an integer converted from the original number to an integer
and then conver the decimal number to a varchar (50) to use the STUFF function to remove the ‘0.’
part from the decimal number.
*/
Stuff(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), 1, 2, ”)

/*
Solution 2: Remove the “0.” part from the above calculation with a substring function
*/
,Substring(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), 3, Len(@d))

/*
Solution 3: Use right function to remove the leading “0.” from the string
*/

, RIGHT(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)), Len(Cast(@d – Cast(@d AS INT) AS VARCHAR(50))) – 2)

/*
Solution 4: Use Parsename function to get the value after the first dot.
*/
,Parsename(@s, 1)
/*
Solution 5: Use charindex to get the postion of . and use a substring function to get the value after the decimal
*/
,Substring(@s, Charindex(‘.’, @s) + 1, Len(@s) – Charindex(‘.’, @s))

/*
Solution 6: Find the . position using charindex and stuff everythin before decimal and the deciaml.
*/
,Stuff(@s, 1, Charindex(‘.’, @s), ”)
,Stuff(@s, 1, Charindex(0X2E, Cast(Ltrim(@s) AS VARBINARY(50)), 1), ”)

/*
Solution 7: Reverse the string and find the position of . and use LEF function to get the substring and reverse the substring back.
*/
,Reverse(LEFT(Reverse(@s), Charindex(‘.’, Reverse(@s)) – 1))

/*
Solution 8: Remove everthing before dot with a STUFF function with empty string.
*/
, Stuff(@s, 1, Charindex(‘.’, @s), ”)
/*
Solution 9: CAST the string as an XML type and retrieve the value from the XML node
*/
,Cast (N”
+ Replace(@s, ‘.’, ”)
+ ” AS XML).value(‘(/root/row)[2]’, ‘nvarchar(50)’)
/*
Solution 10: Use the number to MODE the the number converted to an integer and remove the “0.” part afterwards.
Use a case statement to handle the original number smaller than 1.
*/
,Replace(Cast(@d%(CASE WHEN Cast(@d AS INT)=0 THEN 1 END) AS VARCHAR(50)), ‘0.’, ”)

/*
Solution 11: Replace the whole digit part if ther is any and the decimal point.
*/

,Replace(@s,cast(Cast(@d AS INT) as varchar(50))+’.’,”)
/*
Solution 12: Get the value of decimal part and use 10’s power to remove the decimal points
*/

,CAST((@d – Cast(@d AS INT))* POWER(10,len(Cast(@d – Cast(@d AS INT) AS VARCHAR(50)))-2) as int)
,CAST((@d – Cast(@d AS INT))* POWER(10,len(@d – Cast(@d AS INT))-2) as int)


UNPIVOT with Table Value Constructor

Here is a sample to compare function UNPIVOT with Table Value Constructor in SQL Server 2008 and above.

CREATE TABLE Dates (id INT IDENTITY(1, 1),date1 DATETIME,date2 DATETIME,date3 DATETIME)

INSERT INTO Dates
(date1,date2,date3)
VALUES ('1/1/2012','1/2/2012','1/3/2012'),
('1/1/2012',NULL,'1/13/2012'),
('1/1/2012','1/2/2012',NULL),
('8/30/2012','9/10/2012','1/1/2013')

--Table Value
Select ID, MyDate, Date123
FROM Dates A
Cross Apply ( Values (Date1,'Date1'), (Date2,'Date2'), (Date3,'Date3')) B(MyDate, Date123)

--UNPIVOT
SELECT ID, MyDate ,date123 FROM (SELECT id,date1,date2, date3 FROM Dates) src
UNPIVOT (MyDate
FOR date123 IN ([date1],
[date2],
[date3])) unpvt



A Sample with Table Value Constructor (Transact-SQL)

There are a few ways to find maximum value from multiple columns:
http://blogs.msdn.com/b/samlester/archive/2012/09/10/tsql-solve-it-your-way-finding-the-max-value-from-multiple-columns.aspx

Steven has a good example with Table Value Constructor:
Select ID, Date1, Date2, Date3, MaxDate
FROM Dates A Cross Apply (Select Max(MyDate) As MaxDate
From ( Values (Date1), (Date2), (Date3)) B(MyDate) ) C

http://technet.microsoft.com/en-us/library/dd776382.aspx


Toggle a Column Value with T-SQL (bitwise)

UPDATE tableA
SET    Gender = CASE
                  WHEN ( CASE
                           WHEN gender = 'M' THEN 0
                           ELSE 1
                         END ^1 ) = 0 THEN 'M'
                  ELSE 'F'
                END 



Timestamp Update — A Sample

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7c2985fc-1ede-43e4-bd40-9b66f3bf7fda

DECLARE @s TABLE ( Startdt DATETIME,Hours DECIMAL(6, 2))

INSERT INTO @s
VALUES (‘2012-08-09 23:00:00.000’,8.00),
(‘2012-08-09 07:00:00.000’,8.00),
(‘2012-08-09 15:00:00.000’,0.80),
(‘2012-08-09 15:00:00.000’,7.00),
(‘2012-08-09 15:00:00.000’,0.20),
(‘2012-08-10 23:00:00.000’,5.40),
(‘2012-08-10 23:00:00.000’,2.60);

WITH mycte
AS (SELECT startdt,Hours,ROW_NUMBER() OVER(Partition BY startdt ORDER BY startdt) rn FROM @s)

,mycte2
AS (SELECT startdt,MAX([1]) AS c1,MAX([2]) AS c2,MAX([3]) AS c3
FROM (SELECT startdt, Hours, rn FROM mycte) src
PIVOT (MAX(hours) FOR rn IN ([1], [2], [3])) pvt
GROUP BY startdt)

SELECT CASE WHEN col = ‘c1’ THEN startdt
WHEN col = ‘c2’ THEN e1
WHEN col = ‘c3’ THEN e2
END AS stardt,
Hours,
CASE WHEN col = ‘c1’ THEN e1
WHEN col = ‘c2’ THEN e2
WHEN col = ‘c3’ THEN e3
END AS enddt
FROM (SELECT startdt,c1,c2,c3,DATEADD(minute, c1 * 60, startdt) e1,
DATEADD(minute,( c1 + c2 ) * 60, startdt) e2,DATEADD(minute,( c1 + c2 + c3 ) * 60, startdt) e3 FROM mycte2)src
UNPIVOT(Hours FOR col IN (c1, c2, c3)) unpvt

–Result
/*

2012-08-09 07:00:00.000 8.00000 2012-08-09 15:00:00.000
2012-08-09 15:00:00.000 0.80000 2012-08-09 15:48:00.000
2012-08-09 15:48:00.000 7.00000 2012-08-09 22:48:00.000
2012-08-09 22:48:00.000 0.20000 2012-08-09 23:00:00.000
2012-08-09 23:00:00.000 8.00000 2012-08-10 07:00:00.000
2012-08-10 23:00:00.000 5.40000 2012-08-11 04:24:00.000
2012-08-11 04:24:00.000 2.60000 2012-08-11 07:00:00.000

*/


Check Rows’ NULL Percentage For a Given Table –T-SQL

–USE YourDB
–GO
DECLARE @colsCast NVARCHAR(4000)
, @cols NVARCHAR(4000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) =’dbo’
, @TABLE_NAME NVARCHAR(4000) =’test1′
,@num NVARCHAR(4000)

SELECT @colsCast = COALESCE(@colsCast + ‘, ‘, ”) + ‘CAST(‘+ Quotename(column_Name)+’ AS NVARCHAR(4000)) AS ‘+ Quotename(column_Name)
, @cols = COALESCE(@cols + ‘, ‘, ”) + Quotename(column_Name) , @num=count(*) over()
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
print @cols

–You can modify the WHERE clause to filter your original table

SET @sql1=’IF OBJECT_ID(N”dbo.tmp”,N”U”) IS NOT NULL
Begin
DROP Table dbo.tmp
END

Begin
SELECT * INTO tmp
FROM ‘+ Quotename(@TABLE_NAME) + ‘ WHERE 1=1
END’

EXEC sp_executesql @sql1

SET @sql2=’WITH mycte AS
(
SELECT rn, col, val FROM (SELECT ‘+ @colsCast+ ‘, row_number() OVER(Order by ‘ + @cols+’ ) rn FROM tmp) src
UNPIVOT (val For col IN ( ‘+ @cols+ ‘)) unpvt )
, mycte2 AS ( SELECT rn, col, val, COUNT(val) OVER(Partition by rn) cnt , ‘+@num+’ as cntAll FROM mycte )
, mycte3 AS (SELECT rn, col, val, row_number() OVER(Order by cnt DESC ) rn2 , CAST(cnt*1.0/ cntALL*100 as decimal(6,2)) as NullPercentForRow FROM mycte2)

SELECT ‘+ @cols+ ‘, NullPercentForRow from (select rn, col, val, NullPercentForRow FROM mycte3
–WHERE rn=(select rn from mycte3 WHERE rn2=1 )
) src
PIVOT (max(val) FOR col IN ( ‘+ @cols+ ‘)) pvt ‘

print @sql2

EXEC sp_executesql @sql2


Pivot Table with T-SQL Dynamically– Another Sample

CREATE TABLE [dbo].[Test1](
[id] [int] IDENTITY(1,1) NOT NULL,
[PersonName] [varchar](50) NULL,
[PaymentDate] [datetime] NULL,
[Paymentamount] [int] NULL
) ON [PRIMARY]

INSERT[dbo].[Test1]
VALUES (‘A’,’1/1/2012′,100),
(‘B’,’1/2/2012′,200),
(‘C’,’1/3/2012′,300),
(‘D’,’1/4/2012′,400),
(‘E’,’1/2/2012′,500),
(‘A’,’2/1/2012′,500),
(‘B’,’2/2/2012′,300),
(‘C’,’2/3/2012′,100),
(‘D’,’2/4/2012′,200),
(‘E’,’2/5/2012′,400),
(‘A’,’3/1/2012′,200),
(‘B’,’3/3/2012′,100),
(‘C’,’3/3/2012′,300),
(‘D’,’3/4/2012′,500),
(‘E’,’3/5/2012′,400)

–Code
DECLARE @cols NVARCHAR(4000), @sql NVARCHAR(4000)

SELECT @cols = COALESCE(@cols + ‘,’, ” )+ ‘[‘+shortname+’]’ FROM (
SELECT PaymentDate, STUFF(CONVERT(VARCHAR(15), PaymentDate, 107), 4, 7, ‘-‘) AS shortName FROM test1 ) t GROUP BY shortname Order by MIN(PaymentDate)

–PRINT @cols

SET @sql = ‘SELECT PersonName ,’ +@cols + ‘ FROM ( SELECT PersonName,Paymentamount, STUFF(CONVERT(VARCHAR, [PaymentDate], 107), 4, 7, ”-”) AS shortname FROM Test1) src
PIVOT( SUM(Paymentamount) FOR shortname IN (‘ + @cols + ‘)) pvt ORDER BY PersonName ‘

EXEC sp_executesql @sql