Table of Contents Link to heading

• What is a Function
• Working with String
  • Example:
• SUBSTRING function
  • Syntax
  • Arguments
  • Example:
• CHARINDEX function
  • Syntax
  • Arguments
  • Example
• Example of working with string
• Working with date
  • Arguments
  • DATEPART() vs. DATENAME()
  • Example
• CAST function
  • Syntax
  • Arguments
  • Examples
• ISNULL function
  • Syntax
  • Examples

What is a Function Link to heading

Functions are calculations performed by the Database Management System (DBMS).

Working with String Link to heading

Common functions include:

Example: Link to heading

SELECT UPPER ('Eynesbury') AS [School Name]

Result:

SUBSTRING function Link to heading

Syntax Link to heading

SUBSTRING ( expression, start, length )

• Returns part of a character or text in SQL server.

Arguments Link to heading

1. expression

• A character or text.

2. start

• An integer that specifies where the returned characters start.
• If start is less than 1, the returned expression will begin at the first character specified in expression.
• If start is greater than the number of characters in the value expression, a zero-length expression is returned.

3. length

• A positive integer that specifies how many characters of the expression will be returned.
• If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.
• If length is negative, an error is raised and the statement is terminated.

Example: Link to heading

SELECT SUBSTRING ('Eynesbury', 1, 2) AS [School Name];

Result:

CHARINDEX function Link to heading

Syntax Link to heading

CHARINDEX ( expressionToFind, expressionToSearch [ , start_location ] )

• Searches an expression for another expression and returns its starting position if found.

Arguments Link to heading

1. expressionToFind

• A character expression that contains the sequence to be found.
• expressionToFind is limited to 8000 characters.

2. expressionToSearch

• A character expression to be searched.

3. start_location

• An integer at which the search starts.
• If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.

Example Link to heading

SELECT CHARINDEX ('Fun', 'DatabaseFundamentals') AS counting;

Result:

Example of working with string Link to heading

DECLARE @address varchar(100) = '13 Wayville road, Woodville, SA 5000'

SELECT
    LEFT(@address, CHARINDEX(',', @address) - 1) AS streetAddress,
    LEFT(secondPart, LEN(secondPart) - CHARINDEX(' ', REVERSE(secondPart)) -1) AS suburb,
    RIGHT(secondPart, CHARINDEX(' ', REVERSE(secondPart))) AS state,
    REVERSE(SUBSTRING(REVERSE(@address), 1, 4)) AS postcode
    FROM (
    SELECT
        RTRIM(
    REVERSE(
        SUBSTRING(
            REVERSE(@address), 6, LEN(@address) - CHARINDEX(',', @address) – 5
                  )
        )
    ) AS secondPart
) AS t1;

Result:

Working with date Link to heading

Below is a list of common functions related to dates:

Today is Monday, 28 March, 2022.

Arguments Link to heading

1. inputDate

• A literal date or an expression that can resolve to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

2. datePart

• A part of the date that you want to return.
• The table below lists all valid date part values.
• Note: either upper-case or lower-case letters are acceptable.

DATEPART() vs. DATENAME() Link to heading

DATENAME() is similar to the DATEPART(), except for the return type.

1. DATENAME() returns the date part as a character string.
2. DATEPART() returns the date part as an integer.

Example Link to heading

SELECT
    DATEPART(year, '2022-03-28') + '1' [datePart],
    DATENAME(year, '2022-03-28') + '1' [dateName] ;

Result:

:link: SQL Server Tutorial

CAST function Link to heading

Syntax Link to heading

CAST ( expression AS datatype [ ( length ) ] )

• Converts an expression (of any type) into a specified data type.

Arguments Link to heading

1. expression

• Any valid expression.

2. datatype

• The target data type, such as int, varchar, bit, etc.

3. length

• An optional integer that specifies the length of the target data type.
• The default value is 30.

Examples Link to heading

:memo: Change a value to text.

SELECT
  CAST (COUNT (A.actorID) AS varchar (50)) AS example1
FROM Actor as A;

:memo: Change a number to text.

SELECT CAST (5 AS varchar(50)) AS example2

:memo: Add dollar sign before money values.

SELECT
    '$' + CAST (MAX (E.salary) AS varchar(40)) AS example3
FROM Employee AS E;

ISNULL function Link to heading

Syntax Link to heading

ISNULL (expression_or_attribute, replacement_value)

• Returns a specified value if the expression is NULL.
• If the expression is NOT NULL, then returns the expression.

Examples Link to heading

SELECT ISNULL ('Hello', 'tandukemai.com') AS example1;

Result

SELECT ISNULL (NULL, 'tandukemai.com') AS example2;

Result

:memo: Find ALL Simpsons characters and their first aired episode; otherwise, display ‘TBA’.

SELECT
    CharacterName,
    ISNULL (EpisodeName, 'TBA') AS FirstEpisode
FROM Character AS C LEFT OUTER JOIN Episode AS E
ON C.EpisodeID = E.EpisodeID

Sample result: