SQL- Function to Convert Amount to Words

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : To get the amount in words
-------------------------------------------------
--SELECT dbo.AmountInWords (999999999)
-- ==============r===============================

ALTER FUNCTION [dbo].[AmountInwords] (
@numAmount NUMERIC(19,8)
)
RETURNS VARCHAR(8000)
--WITH ENCRYPTION
BEGIN
DECLARE @x NUMERIC(38,8)
DECLARE @y NUMERIC(38,8)
DECLARE @incr BIGINT
DECLARE @incr2 BIGINT
DECLARE @Remainder BIGINT
DECLARE @Remainder2 BIGINT
DECLARE @Integer BIGINT
DECLARE @MoneyinWords VARCHAR(MAX)
SET @MoneyinWords = ''
SET @x =@numAmount
SET @incr = 1
SET @incr2 = 1

DECLARE @tblDeult TABLE(val BIGINT , valstr VARCHAR(MAX))
INSERT @tblDeult
SELECT 1 , 'One'
UNION
SELECT 2 , 'Two'
UNION
SELECT 3 , 'Three'
UNION
SELECT 4, 'Four'
UNION
SELECT 5 , 'Five'
UNION
SELECT 6, 'Six'
UNION
SELECT 7 , 'Seven'
UNION
SELECT 8 , 'Eight'
UNION
SELECT 9 , 'Nine'
UNION
SELECT 10 , 'Ten'
UNION
SELECT 11 , 'Eleven'
UNION
SELECT 12 , 'Twelve'
UNION
SELECT 13, 'Thirteen'
UNION
SELECT 14 , 'Fourteen'
UNION
SELECT 15 , 'Fifteen'
UNION
SELECT 16 , 'Sixteen'
UNION
SELECT 17 , 'Seventeen'
UNION
SELECT 18 , 'Eighteen'
UNION
SELECT 19 , 'Nineteen'
UNION
SELECT 20 , 'Twenty'
UNION
SELECT 30 , 'Thirty'
UNION
SELECT 40 , 'Fourty'
UNION
SELECT 50 , 'Fifty'
UNION
SELECT 60 , 'Sixty'
UNION
SELECT 70 , 'Seventy'
UNION
SELECT 80, 'Eighty'
UNION
SELECT 90 , 'Ninety'
UNION
SELECT 100 , 'Hundred'
UNION
SELECT 1000 , 'Thousand'
UNION
SELECT 100000 , 'Lakhs'
UNION
SELECT 10000000 , 'Crores'


WHILE (@incr > 0)
BEGIN
IF (@x < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @x

SET @incr = 0
END
ELSE IF (@x > 20 AND @x < 100)
BEGIN
SET @Remainder = @x % 10
SET @Integer = (@x /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99 AND @x <1000 )
BEGIN
SET @Remainder = @x % 100
SET @Integer = (@x /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 999 AND @x <100000 )
BEGIN
SET @Remainder = @x % 1000
SET @Integer = (@x /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99999 AND @x <10000000 )
BEGIN
SET @Remainder = @x % 100000
SET @Integer = (@x /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 9999999)-- AND @x <1000000000 )
BEGIN
SET @Remainder = @x % 10000000
SET @Integer = (@x /10000000)
SET @y = @Integer
SET @Remainder2 = @Remainder
IF (@Integer > 19)
BEGIN
WHILE ( @incr2> 0 )
BEGIN
IF (@y < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @y

SET @incr2 = 0
END
ELSE IF (@y > 20 AND @y < 100)
BEGIN
SET @Remainder2= @y % 10
SET @Integer = (@y /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99 AND @y <1000 )
BEGIN
SET @Remainder2= @y % 100
SET @Integer = (@y /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 999 AND @y <100000 )
BEGIN
SET @Remainder2= @y % 1000
SET @Integer = (@y /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99999 AND @y <10000000 )
BEGIN
SET @Remainder2= @y % 100000
SET @Integer = (@y /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
END

END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 10000000

SET @incr = @incr +1
SET @x = @Remainder
END

END

SELECT @MoneyinWords = @MoneyinWords + ' Rupees Only'


RETURN @MoneyinWords
END

SQL - INITCap Function

-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To make upper case for Each word
-- =============================================
ALTER FUNCTION [dbo].[INITCAP_date]
(
@vchDateString VARCHAR(MAX)
) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @bitValidDate BIT
SET @vchDateString = REPLACE(@vchDateString,' ','')
-- a valid date should be min Eight chars and Max
IF (LEN( @vchDateString) > 7) AND (LEN(@vchDateString) < 24)
BEGIN
SET @bitValidDate = ISDATE(@vchDateString)
END
ELSE
BEGIN
SET @bitValidDate = 0
END

RETURN @vchDateString
END

SQL- To list the hierarchy date

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To List list the dates between startdate and end date
-- =============================================
ALTER FUNCTION [dbo].[udf_HierarchyDate]
(
@StartDate DATETIME
, @EndDate DATETIME
)
RETURNS @date TABLE(DATE DATETIME) AS
BEGIN
-- process until startdate is equal to end date
WHILE (@StartDate<=@EndDate)
BEGIN
INSERT INTO @date
SELECT @StartDate
-- increment the date
SET @StartDate = @StartDate+1
END

RETURN

END

SQL-Split String Values

-- =============================================
-- Author: S.Ramesh
-- Create date: 07-05-2006
-- Description: To split the string as separate parts
-- =============================================

ALTER FUNCTION [dbo].[udf_SplitString]
(
@vchString VARCHAR(MAX)
, @vchDelimitter CHAR(1)= ','
)
RETURNS @Values TABLE (Parts VARCHAR(MAX))AS
BEGIN
DECLARE @intPosistion INT -- to hold the string posistion
DECLARE @Part VARCHAR(MAX)
SELECT @intPosistion = 1
WHILE (@intPosistion > 0)
BEGIN
-- check the given delimitter in between names else consider it as single string
SELECT @intPosistion = CHARINDEX(@vchDelimitter,@vchString)
IF @intPosistion > 0
BEGIN
-- then split the name
SELECT @Part = LEFT(@vchString,@intPosistion - 1)
END
ELSE
BEGIN
-- if string doesnt have specified delimitter then take it as single string
SELECT @Part = @vchString
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
-- insert the splitted part to return as row value
INSERT @Values(Parts)
SELECT LTRIM(RTRIM(@Part))
SELECT @vchString = RIGHT(@vchString,LEN(@vchString) - @intPosistion)
-- if there are no chanraters in given string then end loop
IF LEN(@vchString) = 0
BEGIN
-- Set posistion = -1 to end loop
SELECT @intPosistion = -1
END
END
RETURN
END

SQL Server val Function Equivalent to MS Access Val function

RemoveCharValues

-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : to remove the char values
-------------------------------------------------

-- ==============r===============================
CREATE FUNCTION [dbo].[RemoveCharValues] (
@vchString VARCHAR(MAX)
)
RETURNS VARCHAR(8000)
--WITH ENCRYPTION
BEGIN
-- find if character exists remove the character
WHILE (PATINDEX('%[A-Z]%',@vchString))>0
BEGIN
SET @vchString = REPLACE(@vchString,SUBSTRING(@vchString,PATINDEX('%[A-Z]%',@vchString),1),'')
END
-- return the numeric part
RETURN @vchString
END