Data & Database & sql- class 1

System Functions in SQL class 7

Functions
System functions
User-defined functions

Function	Explanation
ASCII	Returns the ASCII code of the given character expression. 0 to 255 values
CHAR	Converts an int ASCII code to a character. The Integer_Expression should be between 0 and 255 Ex: print A to Z
LTRIM	Removes blanks on the left handside of the given character expression Syntax: LTRIM() Ex:LTRIM(‘ Zukhro’) >> ‘Zukhro’
RTRIM	Removes blanks on the right hand side of the given character expression Syntax: RTRIM() Ex: RTRIM(‘John ’) >> ‘John’
LOWER	Converts all the characters in the given Character_Expression to lowercase letters Syntax: Lower() Ex: Lower(‘JANnet’) >> ‘jannet’
UPPER	Converts all the characters in the given Character_Expression to uppercase letters Syntax: Upper() Ex: Upper(‘JANnet’) >> ‘JANNET’

Function	Explanation
REVERSE	Reverses all the characters in the given string expression (string expression) Syntax: Reverse() Ex: Reverse(‘Mike’) >> ‘ekiM’
LEN	Returns the count of total characters, in the given string(string expression) Syntax: Len() Ex: Len(‘Hello’) >> 5
LEFT()	Returns the left part of a character string with the specified number of characters Syntax: LEFT(string, number_of_chars) Ex: Left(‘Hello’, 2) >> He
Right()	Returns the right part of a character string with the specified number of characters Syntax: RIGHT(string, number_of_chars) Right(‘Hello’, 2) >> lo
CHARINDEX()	This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found Syntax: CHARINDEX(substring_to_search, string, start) Ex: Charindex(‘H’, ‘Hello’) >> 1
SUBSTRING()	Extracts part of a character Syntax: SUBSTRING(string, start, length) Ex: Substring(‘Hello’, 1, 2) >> He
Replicate	Repeats a string the specified number of times Syntax: REPLICATE(string, integer) Ex: Replicate(‘Hello’, 3) >>> Hello Hello Hello

Function	Explanation
Space	Returns a string of the specified number of spaces Syntax: SPACE(number)
Patindex	Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types Syntax: PATINDEX ( '%pattern%' , expression )
Replace	Replaces occurrences of a specified string Syntax: REPLACE(string, old_string, new_string) Ex: SELECT REPLACE('[e-mail]','john','xxx') >> [e-mail]
STUFF	It inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. Syntax: STUFF ( character_expression , start , length , replaceWith_expression ) Ex: STUFF(‘[e-mail]’, 2, 3, '****') >> ‘j****@gmail.com’

Thank you