KEMBAR78
4function Programs | PDF | Chess Theory | Chess
0% found this document useful (0 votes)
2 views9 pages

4function Programs

The document contains a series of User Defined Functions (UDFs) written in SQL for various mathematical and string operations. Each function is defined with a specific purpose, such as addition, subtraction, and checking if a number is even or odd, along with example usage. The functions cover a wide range of functionalities including arithmetic calculations, string manipulations, and conversions.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
2 views9 pages

4function Programs

The document contains a series of User Defined Functions (UDFs) written in SQL for various mathematical and string operations. Each function is defined with a specific purpose, such as addition, subtraction, and checking if a number is even or odd, along with example usage. The functions cover a wide range of functionalities including arithmetic calculations, string manipulations, and conversions.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 9

**1.

UDF to Add Two Numbers**

CREATE FUNCTION AddNumbers (@num1 INT, @num2 INT)


RETURNS INT
AS
BEGIN
RETURN (@num1 + @num2)
END

-- Example usage:
SELECT dbo.AddNumbers(2, 3); -- Output: 5

**2. UDF to Subtract Two Numbers**

CREATE FUNCTION SubtractNumbers (@num1 INT, @num2 INT)


RETURNS INT
AS
BEGIN
RETURN (@num1 - @num2)
END

-- Example usage:
SELECT dbo.SubtractNumbers(7, 5); -- Output: 2

**3. UDF to Multiply Two Numbers**

CREATE FUNCTION MultiplyNumbers (@num1 INT, @num2 INT)


RETURNS INT
AS
BEGIN
RETURN (@num1 * @num2)
END

-- Example usage:
SELECT dbo.MultiplyNumbers(4, 5); -- Output: 20

**4. UDF to Divide Two Numbers**

CREATE FUNCTION DivideNumbers (@num1 INT, @num2 INT)


RETURNS FLOAT
AS
BEGIN
RETURN (@num1 * 1.0 / @num2)
END

-- Example usage:
SELECT dbo.DivideNumbers(10, 2); -- Output: 5.0

**5. UDF to Check If a Number is Even**

CREATE FUNCTION IsEven (@number INT)


RETURNS BIT
AS
BEGIN
IF @number % 2 = 0
RETURN 1
ELSE
RETURN 0
END

-- Example usage:
SELECT dbo.IsEven(4); -- Output: 1 (Even)

**6. UDF to Check If a Number is Odd**

CREATE FUNCTION IsOdd (@number INT)


RETURNS BIT
AS
BEGIN
IF @number % 2 = 1
RETURN 1
ELSE
RETURN 0
END

-- Example usage:
SELECT dbo.IsOdd(3); -- Output: 1 (Odd)

**7. UDF to Get the Square of a Number**

CREATE FUNCTION GetSquare (@number INT)


RETURNS INT
AS
BEGIN
RETURN (@number * @number)
END

-- Example usage:
SELECT dbo.GetSquare(5); -- Output: 25

**8. UDF to Get the Cube of a Number**

CREATE FUNCTION GetCube (@number INT)


RETURNS INT
AS
BEGIN
RETURN (@number * @number * @number)
END

-- Example usage:
SELECT dbo.GetCube(2); -- Output: 8

**9. UDF to Find Maximum of Two Numbers**

CREATE FUNCTION MaxNumber (@num1 INT, @num2 INT)


RETURNS INT
AS
BEGIN
IF @num1 > @num2
RETURN @num1
ELSE
RETURN @num2
END

-- Example usage:
SELECT dbo.MaxNumber(3, 7); -- Output: 7

**10. UDF to Find Minimum of Two Numbers**

CREATE FUNCTION MinNumber (@num1 INT, @num2 INT)


RETURNS INT
AS
BEGIN
IF @num1 < @num2
RETURN @num1
ELSE
RETURN @num2
END

-- Example usage:
SELECT dbo.MinNumber(3, 7); -- Output: 3

**11. UDF to Calculate Factorial of a Number**

CREATE FUNCTION Factorial (@number INT)


RETURNS INT
AS
BEGIN
DECLARE @result INT = 1
WHILE @number > 1
BEGIN
SET @result = @result * @number
SET @number = @number - 1
END
RETURN @result
END

-- Example usage:
SELECT dbo.Factorial(4); -- Output: 24

**12. UDF to Find the Power of a Number**

CREATE FUNCTION PowerNumber (@base INT, @exponent INT)


RETURNS INT
AS
BEGIN
RETURN (POWER(@base, @exponent))
END

-- Example usage:
SELECT dbo.PowerNumber(2, 3); -- Output: 8

**13. UDF to Concatenate First and Last Name**


CREATE FUNCTION FullName (@firstName VARCHAR(50), @lastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
RETURN (@firstName + ' ' + @lastName)
END

-- Example usage:
SELECT dbo.FullName('John', 'Doe'); -- Output: John Doe

**14. UDF to Check if a Number is Prime**

CREATE FUNCTION IsPrime (@number INT)


RETURNS BIT
AS
BEGIN
DECLARE @i INT = 2
IF @number <= 1
RETURN 0
WHILE @i <= @number / 2
BEGIN
IF @number % @i = 0
RETURN 0
SET @i = @i + 1
END
RETURN 1
END

-- Example usage:
SELECT dbo.IsPrime(5); -- Output: 1 (Prime)

**15. UDF to Convert Celsius to Fahrenheit**

CREATE FUNCTION CelsiusToFahrenheit (@celsius FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (@celsius * 9 / 5) + 32
END

-- Example usage:
SELECT dbo.CelsiusToFahrenheit(25); -- Output: 77

**16. UDF to Convert Fahrenheit to Celsius**

CREATE FUNCTION FahrenheitToCelsius (@fahrenheit FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (@fahrenheit - 32) * 5 / 9
END

-- Example usage:
SELECT dbo.FahrenheitToCelsius(77); -- Output: 25
**17. UDF to Reverse a String**

CREATE FUNCTION ReverseString (@input VARCHAR(100))


RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @reversed VARCHAR(100) = ''
DECLARE @i INT = LEN(@input)
WHILE @i > 0
BEGIN
SET @reversed = @reversed + SUBSTRING(@input, @i, 1)
SET @i = @i - 1
END
RETURN @reversed
END

-- Example usage:
SELECT dbo.ReverseString(''); -- Output: LQS

**18. UDF to Count Vowels in a String**

CREATE FUNCTION CountVowels (@input VARCHAR(100))


RETURNS INT
AS
BEGIN
DECLARE @vowelCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) IN ('a', 'e', 'i', 'o', 'u', 'A', 'E', 'I',
'O', 'U')
SET @vowelCount = @vowelCount + 1
SET @i = @i + 1
END
RETURN @vowelCount
END

-- Example usage:
SELECT dbo.CountVowels('Programming'); -- Output: 3

**19. UDF to Count Consonants in a String**

CREATE FUNCTION CountConsonants (@input VARCHAR(100))


RETURNS INT
AS
BEGIN
DECLARE @consonantCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) NOT IN ('a', 'e', 'i', 'o', 'u', 'A', 'E', 'I',
'O', 'U')
SET @consonantCount = @consonantCount + 1
SET @i = @i + 1
END
RETURN @consonantCount
END
-- Example usage:
SELECT dbo.CountConsonants('Programming'); -- Output: 7

**20. UDF to Calculate the Area of a Circle**

CREATE FUNCTION AreaOfCircle (@radius FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (3.14159 * @radius * @radius)
END

-- Example usage:
SELECT dbo.AreaOfCircle(7); -- Output: 153.938

**21. UDF to Calculate the Area of a Rectangle**

CREATE FUNCTION AreaOfRectangle (@length FLOAT, @width FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (@length * @width)
END

-- Example usage:
SELECT dbo.AreaOfRectangle(10, 5); -- Output: 50

**22. UDF to Calculate the Area of a Triangle**

CREATE FUNCTION AreaOfTriangle (@base FLOAT, @height FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (0.5 * @base * @height)
END

-- Example usage:
SELECT dbo.AreaOfTriangle(10, 5); -- Output: 25

**23. UDF to Convert Hours to Minutes**

CREATE FUNCTION HoursToMinutes (@hours INT)


RETURNS INT
AS
BEGIN
RETURN (@hours * 60)
END

-- Example usage:
SELECT dbo.HoursToMinutes(2); -- Output: 120

**24. UDF to Get the Length of a


String**

CREATE FUNCTION GetStringLength (@input VARCHAR(100))


RETURNS INT
AS
BEGIN
RETURN (LEN(@input))
END

-- Example usage:
SELECT dbo.GetStringLength(' Server'); -- Output: 11

**25. UDF to Check If a Number is Even or Odd**

CREATE FUNCTION ChecEvenOdd (@number INT)


RETURNS VARCHAR(4)
AS
BEGIN
RETURN CASE
WHEN @number % 2 = 0 THEN 'Even'
ELSE 'Odd'
END
END
select dbo.ChecEvenOdd(6)

-- Example usage:
SELECT dbo.CheckEvenOdd(5); -- Output: Odd

or

CREATE FUNCTION CheckEvenOdd (@number INT)


RETURNS VARCHAR(4)
AS
BEGIN
DECLARE @result VARCHAR(4)

IF @number % 2 = 0
SET @result = 'Even'
ELSE
SET @result = 'Odd'

RETURN @result
END
SELECT dbo.CheckEvenOdd(5)

**26. UDF to Calculate the Square Root of a Number**

CREATE FUNCTION SquareRoot (@number FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (SQRT(@number))
END

-- Example usage:
SELECT dbo.SquareRoot(25); -- Output: 5.0
**27. UDF to Find the Absolute Value of a Number**

CREATE FUNCTION AbsoluteValue (@number FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (ABS(@number))
END

-- Example usage:
SELECT dbo.AbsoluteValue(-7); -- Output: 7

**28. UDF to Check If a Number is Negative**

CREATE FUNCTION IsNegative (@number INT)


RETURNS BIT
AS
BEGIN
IF @number < 0
RETURN 1
ELSE
RETURN 0
END

-- Example usage:
SELECT dbo.IsNegative(-5); -- Output: 1 (Negative)

**29. UDF to Find the Decimal Part of a Number**

CREATE FUNCTION DecimalPart (@number FLOAT)


RETURNS FLOAT
AS
BEGIN
RETURN (@number - FLOOR(@number))
END

-- Example usage:
SELECT dbo.DecimalPart(7.89); -- Output: 0.89

**30. UDF to Count the Words in a String**

CREATE FUNCTION CountWords (@input VARCHAR(100))


RETURNS INT
AS
BEGIN
DECLARE @wordCount INT = 0
DECLARE @i INT = 1
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) = ' '
SET @wordCount = @wordCount + 1
SET @i = @i + 1
END
RETURN (@wordCount + 1)
END
-- Example usage:
SELECT dbo.CountWords(' Server Functions'); -- Output: 3

You might also like