Scribd
Upload
1 views3 pages

Task 5 Isnull and String Functions

The document explains SQL functions for handling NULL values, including ISNULL(), CASE statements, and COALESCE(). It also covers string functions for data transformation, such as ASCII, CHAR, LTRIM, RTRIM, UPPER, LOWER, REVERSE, and LEN. Additionally, it describes functions like REPLICATE, REPLACE, STUFF, and PATINDEX for string manipulation.

Uploaded by

praveenradiant7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
1 views3 pages

Task 5 Isnull and String Functions

The document explains SQL functions for handling NULL values, including ISNULL(), CASE statements, and COALESCE(). It also covers string functions for data transformation, such as ASCII, CHAR, LTRIM, RTRIM, UPPER, LOWER, REVERSE, and LEN. Additionally, it describes functions like REPLICATE, REPLACE, STUFF, and PATINDEX for string manipulation.

Uploaded by

praveenradiant7
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Task –5

IS NULL ():

ISNULL() replaces NULL with a specified replacement value and if it is not null, it wont replace.

Code:
select e.Name as Employee_name,ISNULL(M.name,'No Manager') As
Manager_name
from Employee e left join Employee m
on e.Managerid=m.Employeeid;

CASE Statement:

Performs conditional logic and returns a value based on whether a condition is true or false.
Syntax used with it are WHEN and THEN.

Code:
select e.name as Employee_name, case when m.name is null then
'No Manager'
else m.Name end as Manager_name
from Employee e left join Employee m
on e.Managerid=m.Employeeid;
COALESCE():
Returns the first non-NULL value from a list of expressions. Very much useful when we deal
with missing data and want to provide fallback options.

Code:
select e.Name as Employee_name,coalesce(M.name,'No Manager') As
Manager_name
from Employee e left join Employee m
on e.Managerid=m.Employeeid;
STRING FUNCTIONS:

String functions are used in data transformation , pattern analyzing , etc,.

➢ ASCII(char) – Returns the ASCII code of the first character in the input string.
➢ CHAR(int) – Returns the character corresponding to the ASCII code provided.
➢ LTRIM(string) – Removes leading spaces from a string.
➢ RTRIM(string) – Removes trailing spaces from a string.
➢ UPPER(string) – Converts all characters in the string to uppercase.
➢ LOWER(string) – Converts all characters in the string to lowercase.
➢ REVERSE(string) – Reverses the order of characters in a string.
➢ LEN(string) – Returns the number of characters in a string (excluding trailing
spaces).

REPLICATE,REPLACE,STUFF ,PATINDEX :

➢ REPLICATE(string, count) – Repeats a string a specified number of times.


➢ SPACE(n) – Returns a string of n spaces.
➢ PATINDEX('%pattern%', string) – Returns the starting position of a pattern in a string
using wildcards.
➢ STUFF(string, start, length, new_string) – Deletes a part of a string and inserts new text
at a specified position.
➢ REPLACE(string, old, new) – Replaces all occurrences of a substring with another
substring.

You might also like