SQL Question 16

Problem

  • USE Northwind
  • Write a scalar userdefined function that will return either the first name or last name The function will accept two parameters, the full name, ie ‘Lew Cousineau’ and an indicator of whether the user wants to return the first or last name. Test the function with the contact name in the customer table of Northwind

Code

USE Northwind
GO
IF OBJECT_ID('fnGetName','FN') IS NOT NULL
DROP FUNCTION fnGetName
GO
CREATE FUNCTION fnGetName(@FullName varchar(100), @FirstorLast varchar(5))
RETURNS varchar(100)
AS
BEGIN
DECLARE @spacePosition int
DECLARE @TheName varchar(100)
SET @spacePosition = CHARINDEX(' ', @FullName)
IF @firstorLast = 'First'
SET @TheName = SUBSTRING(@FullName, 1,@spaceposition-1)
ELSE
SET @TheName = SUBSTRING(@FullName, @spacePosition +1,@spaceposition-1)
RETURN @TheName
END

-- To test the User Defined Function

GO
SELECT c.ContactName, dbo.fnGetName(c.ContactName, 'First')'FirstName'
,dbo.fnGetName(c.ContactName, 'Last')'LastName'
FROM Customers c

Result

Question 16 Result

Question 16 Result

Notes

The function accepts two parameters. The first is @fullname which is the full name of the individual. The second parameter is @FirstorLast which is the choice for the first or last name. The function SET @spacePosition equal to the CHARINDEX position of the blank space in the full name. This is to find the division between first and last name. This @spacePosition is used in an IF and ELSE statement to test if either the first or last name was selected to be returned in the result. The function then returns the first or last name as the @TheName variable. The function is END and then tested with a SELECT and FROM statement.

Comments are closed.