[go: up one dir, main page]
Scribd
Upload
28 views19 pages

Lec14 - Lab - CSC371 - Database Systems

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.

Uploaded by

sp22-bse-097
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
28 views19 pages

Lec14 - Lab - CSC371 - Database Systems

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.

Uploaded by

sp22-bse-097
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 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