SQL SERVER – Query to Convert Numbers into Words

                                           Convert Money Numbers into Strings

CREATE FUNCTION fnNumberToWords
(
    @Number AS BIGINT
) RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))
    DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))
    DECLARE @BelowHundred AS VARCHAR(126) 
    
    INSERT @Below20 (Word) VALUES ('ZERO')
    INSERT @Below20 (Word) VALUES ('ONE')
    INSERT @Below20 (Word) VALUES ( 'TWO' )
    INSERT @Below20 (Word) VALUES ( 'THREE')
    INSERT @Below20 (Word) VALUES ( 'FOUR' )
    INSERT @Below20 (Word) VALUES ( 'FIVE' )
    INSERT @Below20 (Word) VALUES ( 'SIX' )
    INSERT @Below20 (Word) VALUES ( 'SEVEN' )
    INSERT @Below20 (Word) VALUES ( 'EIGHT')
    INSERT @Below20 (Word) VALUES ( 'NINE')
    INSERT @Below20 (Word) VALUES ( 'TEN')
    INSERT @Below20 (Word) VALUES ( 'ELEVEN' )
    INSERT @Below20 (Word) VALUES ( 'TWELVE' )
    INSERT @Below20 (Word) VALUES ( 'THIRTEEN' )
    INSERT @Below20 (Word) VALUES ( 'FOURTEEN')
    INSERT @Below20 (Word) VALUES ( 'FIFTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SIXTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SEVENTEEN')
    INSERT @Below20 (Word) VALUES ( 'EIGHTEEN' )
    INSERT @Below20 (Word) VALUES ( 'NINETEEN' )
 
    INSERT @Below100 VALUES ('TWENTY')
    INSERT @Below100 VALUES ('THIRTY')
    INSERT @Below100 VALUES ('FORTY')
    INSERT @Below100 VALUES ('FIFTY')
    INSERT @Below100 VALUES ('SIXTY')
    INSERT @Below100 VALUES ('SEVENTY')
    INSERT @Below100 VALUES ('EIGHTY')
    INSERT @Below100 VALUES ('NINETY')
 
    IF @Number > 99
    BEGIN
        SELECT @belowHundred = dbo.fnNumberToWords( @Number % 100)
    END
 
    DECLARE @NumberInWords VARCHAR(MAX)
    SET @NumberInWords  = 
    (
      SELECT
        CASE 
            WHEN @Number = 0 THEN  ''
 
            WHEN @Number BETWEEN 1 AND 19 
                THEN (SELECT Word FROM @Below20 WHERE ID=@Number)
 
            WHEN @Number BETWEEN 20 AND 99
                THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' + dbo.fnNumberToWords( @Number % 10) 
 
            WHEN @Number BETWEEN 100 AND 999 
                THEN (dbo.fnNumberToWords( @Number / 100)) + ' HUNDRED '+ 
                        CASE
                            WHEN @belowHundred <> '' 
                                THEN 'AND ' + @belowHundred else @belowHundred
                        END
 
            WHEN @Number BETWEEN 1000 AND 999999 
                THEN (dbo.fnNumberToWords( @Number / 1000))+ ' THOUSAND '+ dbo.fnNumberToWords( @Number % 1000)  
 
            WHEN @Number BETWEEN 1000000 AND 999999999 
                THEN (dbo.fnNumberToWords( @Number / 1000000)) + ' MILLION '+ dbo.fnNumberToWords( @Number % 1000000) 
 
            WHEN @Number BETWEEN 1000000000 AND 999999999999 
                THEN (dbo.fnNumberToWords( @Number / 1000000000))+' BILLION '+ dbo.fnNumberToWords( @Number % 1000000000) 
            
            ELSE ' INVALID INPUT'
        END
    )
 
    SELECT @NumberInWords = RTRIM(@NumberInWords)
 
    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1)='-'
 
    RETURN (@NumberInWords)
 
END

SQL-SERVER Get the first character in each word

The below function will get the string as input and will give each and every character in starting of the words.

Step 1
Create a user defined GetFirstCharacter function,it is look like this

CREATE FUNCTION dbo.GetFirstCharacter(@StringValue nvarchar(max))
RETURNS nvarchar(max)
AS
    BEGIN
    
        DECLARE @FirstChar nvarchar(10) -- get the first character
        DECLARE @StringValueLength int -- length of given string
        DECLARE @Index int  -- Set Start Index of while loop
        DECLARE @Flag bit  -- Check for Blank Space
    
    
        SET @StringValueLength=LEN(@StringValue) -- get lenght of given string
        SET @Index=1 
        SET @Flag=0  
    
        IF ((@StringValue IS NOT NULL) AND (@StringValue<>''))
            BEGIN
                    -- get the first charcter of the first word
                    SET @FirstChar=LEFT(@StringValue,1) 
                    
                    -- get each character by using while loop
                    WHILE (@Index<=@StringValueLength)
                        BEGIN
                            
                            -- Check the blank space 
                            IF(SUBSTRING(@StringValue,@Index,1) = ' ')
                                BEGIN
                                    SET @Flag=1
                                END
                            ELSE
                                BEGIN 
                                    SET @Flag=0
                                END
                            
                            
                            IF(@Flag=1)
                                BEGIN
                                             -- increment the index after the blank space
                                            SET @Index=@Index+1
                                            
                                            -- Get the each character from each word
                                            SET @FirstChar = @FirstChar + SUBSTRING(@StringValue,@Index,1)
                                        
                                        
                                END
                            
                            SET @Index=@Index+1  -- While Counter
                        END
            END 
    
        RETURN @FirstChar -- return the value
    
    END

Step 2
Call a user defined GetFirstCharacter function,it is look like this

SELECT dbo.GetFirstCharacter('Dileep Rawat') AS 'First character of the each word'

SQL SERVER- List all the tables names from the another database

                                   List all the tables names from the another database

Suppose i have a two database like Employee and Department databases.
Currently I am use Northwind database.


So I want to show all the tables names from Department database.
It is look like this;

   1:  Use  Employee

   2:  SELECT * FROM  Department.sys.tables

It will show all the tables names.

SQL SERVER - Working Days Count in MS-SQL

The Following Script to count number of working days between two dates in MS-SQL.

Step 1
Create a User Defined WorkingDaysCount function,it is look like this

CREATE FUNCTION Dbo.WorkingDaysCount(@StartDate DATETIME,@EndDate DATETIME)
RETURNS INT
AS
    BEGIN
        
        DECLARE @CurrentDate DATETIME
        DECLARE @Days INT
        DECLARE @IncrementValue INT
        
            SET @Days=0
            SET @IncrementValue=0
        
                WHILE @IncrementValue<=(DATEDIFF(DAY,@StartDate,@EndDate))    
                    BEGIN
                            -- Get a Current Date between two date
                            SET @CurrentDate=DATEADD(DAY,@IncrementValue,@StartDate)
                            
                            IF (DATENAME(WEEKDAY,@CurrentDate) !='Sunday' )
                                BEGIN
                                        SET @Days=@Days+1 -- Increment Working Days Counts
                                END
                        
                        SET @IncrementValue=@IncrementValue+1
                        
                    END
                    
            RETURN @Days            
    END

Step 2
Call User defined WorkingDaysCount function,it is look like this

SELECT dbo.WorkingDaysCount('1/1/2011','12/31/2011') AS 'Working Days'

Note - Date must Specify on this format - MM/DD/YYYY

Output

SQL Server and Drop all Objects (Tables/Views/Stored Procedures etc.)

The following SQL will drop all SQL Server objects within a database.

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */ DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */ DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */ DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */ DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */ DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO