Scribd Logo
Upload

Welcome to Scribd!

  • 15 views

    Lec14 - Lab - CSC371 - Database Systems

    Uploaded by

    sp22-bse-097
    This document discusses various string functions in SQL Server including CHAR(), CHARINDEX(), CONCAT(), DATALENGTH(), LEFT(), LEN(), LOWER()/UPPER(), LTRIM()/RTRIM(), NCHAR(), REVERSE(), SPACE(), STR(), and SUBSTRING(). It provides the syntax and examples of how each function works.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Lec14 - Lab - CSC371 - Database Systems

    Uploaded by

    sp22-bse-097
    15 views19 pages
    This document discusses various string functions in SQL Server including CHAR(), CHARINDEX(), CONCAT(), DATALENGTH(), LEFT(), LEN(), LOWER()/UPPER(), LTRIM()/RTRIM(), NCHAR(), REVERSE(), SPACE(), STR(), and SUBSTRING(). It provides the syntax and examples of how each function works.

    Original Title

    Lec14_Lab_CSC371_Database Systems

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses various string functions in SQL Server including CHAR(), CHARINDEX(), CONCAT(), DATALENGTH(), LEFT(), LEN(), LOWER()/UPPER(), LTRIM()/RTRIM(), NCHAR(), REVERSE(), SPACE(), STR(), and SUBSTRING(). It provides the syntax and examples of how each function works.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    15 views19 pages

    Lec14 - Lab - CSC371 - Database Systems

    Uploaded by

    sp22-bse-097
    This document discusses various string functions in SQL Server including CHAR(), CHARINDEX(), CONCAT(), DATALENGTH(), LEFT(), LEN(), LOWER()/UPPER(), LTRIM()/RTRIM(), NCHAR(), REVERSE(), SPACE(), STR(), and SUBSTRING(). It provides the syntax and examples of how each function works.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 19

    1

    CSC371-DATABASE SYSTEMS I
    LECTURE-14 (LAB)
    (FALL2023)
    2

    PREVIOUS LECTURE REVIEW

    SQL Stored Procedure


    3
    4

    AGENDA
    SQL Server String Functions
    ASCII CHARACTER 5

    • SELECT fname, ASCII(fname) AS NumCodeOfFirstChar


    • FROM staff;
    SQL SERVER CHAR() FUNCTION 6

    • SELECT CHAR(65) AS CodeToCharacter;


    SQL SERVER CHARINDEX() FUNCTION 7

    • The CHARINDEX() function searches for a substring in a string and returns the position.
    • If the substring is not found, this function returns 0.
    • Note: This function performs a case-insensitive search.

    • CHARINDEX(substring, string, start)


    • SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;
    SQL SERVER CONCAT() FUNCTION 8

    • CONCAT(string1, string2, ...., string_n)


    • SELECT CONCAT('SQL', 'is', 'fun!’);
    • SELECT CONCAT('SQL', ' ', 'is', ' ', 'fun!’);
    SQL SERVER DATALENGTH() FUNCTION 9

    • The DATALENGTH() function returns the number of bytes used to


    represent an expression.
    • Note: The DATALENGTH() function counts both leading and trailing
    spaces when calculating the length of the expression.

    • DATALENGTH(expression)

    • SELECT DATALENGTH(‘ SQL is a fun ‘);


    10

    SQL SERVER LEFT() FUNCTION

    • The LEFT() function extracts a number of characters from a string


    (starting from left).
    • LEFT(string, number_of_chars)
    • SELECT LEFT(lname, 3) AS ExtractString FROM staff;
    11

    SQL SERVER LEN() FUNCTION

    • Count leading spaces but not trailing


    • SELECT LEN(' W3Schools.com ‘);
    12

    SQL SERVER LOWER() / UPPER() FUNCTION

    • SELECT LOWER('SQL Tutorial is FUN!’);

    • SELECT UPPER(‘sql tutorial is a fun’);


    13
    SQL SERVER LTRIM()/RTRIM() FUNCTION

    • The LTRIM() function removes leading spaces from a string.


    • The RTRIM() function remove trailing spaces from a string:
    • LTRIM(string)
    • SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString;
    14

    SQL SERVER NCHAR() FUNCTION

    • The NCHAR() function returns the Unicode character based on the


    number code.
    • NCHAR(number_code)
    • SELECT NCHAR(65) AS NumberCodeToUnicode;
    15

    SQL SERVER REVERSE() FUNCTION

    • The REVERSE() function reverses a string and returns the result.


    • REVERSE(string)

    • SELECT REVERSE(fname) FROM staff


    16

    SQL SERVER SPACE() FUNCTION

    • The SPACE() function returns a string of the specified number of space


    characters.
    • SPACE(number)
    SQL SERVER STR() FUNCTION 17

    • The STR() function returns a number as a string.


    • STR(number, length, decimals)
    • Number: Required. The number to convert to a string
    • Length: Optional. The length of the returning string. Default value is 10
    • Decimal: Optional. The number of decimals to display in the returning string. Default value
    is 0
    • SELECT STR(185.476, 6, 2);
    SQL SERVER SUBSTRING() FUNCTION
    18

    • The SUBSTRING() function extracts some characters from a string.


    • SUBSTRING(string, start, length)
    • String: Required. The string to extract from
    • Start: Required. The start position. The first position in string is 1
    • Length: Required. The number of characters to extract. Must be a positive number
    • SELECT SUBSTRING(address, 1, 10) AS ExtractString FROM privateowner;
    19

    SUMMARY
    SQL Server String Functions

    You might also like