Integer left and right shift

IF OBJECT_ID('dbo.shiftLeft', 'IF') IS NULL
EXEC('CREATE FUNCTION [dbo].[shiftLeft]() RETURNS TABLE AS RETURN SELECT foo = 1')
GO
/***********************************************************************
AUTHOR:Ioannis Tsakpinis
CREATED:29.12.2011
DESCRIPTION :Performs bitwise left-shift: y = x << s
************************************************************************/ALTER FUNCTION [dbo].[shiftLeft] (
@xINT,
@sINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
-- Equivalent to x * 2^s
-- Truncate bits 32-63 and cast back to INT
y = CAST(CAST(@x * p.pow AS BINARY(4)) AS INT)
FROM (
-- 2^31 will overflow, cast to BIGINT
SELECT pow = POWER(CAST(2 AS BIGINT), @s & 0x1F) -- Wrap around 31
) p

-- EOF --
GO
IF OBJECT_ID('dbo.shiftRight', 'IF') IS NULL
EXEC('CREATE FUNCTION [dbo].[shiftRight]() RETURNS TABLE AS RETURN SELECT foo = 1')
GO
IF OBJECT_ID('dbo.shiftRight0', 'IF') IS NOT NULL
DROP FUNCTION [dbo].[shiftRight0]
GO
/***********************************************************************
AUTHOR:Ioannis Tsakpinis
CREATED:29.12.2011
DESCRIPTION :Performs bitwise right-shift with sign-extension: y = x >> s
************************************************************************/ALTER FUNCTION [dbo].[shiftRight] (
@xINT,
@sINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT y = 
CASE WHEN @x >= 0 THEN
-- Equivalent to x / 2^s
CAST(@x / p.pow AS INT)
ELSE
-- Go positive, shift, then go back to negative
CAST(~(~@x / p.pow) AS INT)
END
FROM (
-- 2^31 will overflow, cast to BIGINT
SELECT pow = POWER(CAST(2 AS BIGINT), @s & 0x1F) -- Wrap around 31
) p

-- EOF --
GO
IF OBJECT_ID('dbo.shiftRight0', 'IF') IS NULL
EXEC('CREATE FUNCTION [dbo].[shiftRight0]() RETURNS TABLE AS RETURN SELECT foo = 1')
GO
/***********************************************************************
AUTHOR:Ioannis Tsakpinis
CREATED:29.12.2011
DESCRIPTION :Performs bitwise right-shift with zero-extension: y = x >>> s
************************************************************************/ALTER FUNCTION [dbo].[shiftRight0] (
@xINT,
@sINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT y =
CASE WHEN @x >= 0 THEN
-- Equivalent to right-shift with sign-extension
s.y
ELSE
-- Calculates (x >> s) + (2 << ~s)
-- then truncates bits 32-63 and casts back to INT
CAST(CAST(s.y + (POWER(CAST(2 AS BIGINT), (~@s & 0x1F) + 1)) AS BINARY(4)) AS INT)
END
FROM dbo.shiftRight(@x, @s) s

-- EOF --
GO