| ASCII |
This function accepts a character or character expression as input and returns the ASCII code value of the character. If the input is a character expression then it returns the ASCII code value of the left most character.
Syntax
ASCII(character) or ASCII(character_expression)
|
SELECT ASCII('a')
Output:
97
SELECT ASCII('b')
Output:
98
SELECT ASCII('abc')
Output:
97
|
| CHAR |
It performs just the opposite operation of ASCII function. The CHAR function accepts an integer value within the ASCII code range and returns the character corresponding to the ASCII code.
The input value must be in the range 0 to 255. NULL value will be returned if the input value is outside the range.
Syntax
CHAR(integer)
|
SELECT CHAR(97)
Output:
a
SELECT CHAR(65)
Output:
A
SELECT CHAR(256)
Output:
NULL
|
| NCHAR |
This function takes in an integer value and returns the Unicode character value defined for the integer value by Unicode Standard.
The integer value can be in the range 0 to 65535. Outside this range returns a NULL value.
Syntax
NCHAR(integer)
|
SELECT NCHAR(65)
Output:
A
SELECT NCHAR(66)
Output:
B
SELECT NCHAR(324)
Output:
ń
SELECT NCHAR(65600)
Output:
NULL
|
| CHARINDEX |
The CHARINDEX function accepts two strings and an optional starting position. It Searches string2 for string1 and returns the starting position if string1 is found in string2, otherwise returns zero. If the optional parameter start_position is given then the search begins from the mentioned starting position of string2.
Syntax
CHARINDEX(string1,string2) or CHARINDEX(string1,string2,start_position)
|
SELECT CHARINDEX('blue','The sky is blue.')
Output:
12
SELECT CHARINDEX('s','The sky is blue.',1)
Output:
5
SELECT CHARINDEX('s','The sky is blue.',8)
Output:
10
SELECT CHARINDEX('c','The sky is blue.')
Output:
0
|
| PATINDEX |
This function searches a string for the given pattern. If the given pattern is found in the string then it returns the starting position of the first occurrence of the pattern.
Syntax
PATINDEX('%pattern%',String1)
|
SELECT PATINDEX('% %','The sky is blue.')
Output:
4
SELECT PATINDEX('%is%','The sky is blue.')
Output:
9
|
| SPACE |
This function accepts a positive integer value and returns as many white spaces as the input value.
Syntax
SPACE(integer)
|
SELECT 'Hello'+SPACE(0)+'World'
Output:
HelloWorld
SELECT 'Hello'+SPACE(1)+'World'
Output:
Hello World
|
| STR |
The STR function converts a number from numeric data type to character data type. In other words, it takes in a number and returns the number as a string.
The default output string length will be 10. We can explicitly mention the output string length as the second input parameter.
The third parameter which is optional is used to mention the number of decimal places in the output parameter. The precision of the output string can be up to 16. When the precision value is greater than 16, the output value will be truncated to 16 decimal places.
Syntax
STR(float_number) or STR(float_number, string_length) or STR(float_number, string_length, decimal_precision)
|
SELECT STR(34343.3434343)
Output:
34343
SELECT STR(34343.3434343,5)
Output:
34343
SELECT STR(34343.3434343,9,3)
Output:
34343.343
|
| REPLACE |
It replaces all occurrences of the given string pattern in the source string with the replacement string.
Syntax
REPLACE ( source_string, string_pattern, string_replacement)
|
SELECT REPLACE('The sky is blue.','sky','sea')
Output:
The sea is blue.
|
| STUFF |
The STUFF function removes the characters from source string at the start position to the specified length and then inserts the inserts the string to be stuffed into the source string at the start position.
Syntax
STUFF(source_string, start_position, length, instertion_string)
|
SELECT STUFF('The sky is blue.',5,3,'sea')
Output:
The sea is blue.
|
| REPLICATE |
This REPLICATE function accepts a string as input and repeats the given string for the specified number of times.
Syntax
REPLICATE(string1, number_of_repetitions)
|
SELECT REPLICATE('A',3)
Output:
AAA
|
| SUBSTRING |
The SUBSTRING function can be used to get a part of a string.
Syntax
SUBSTRING(string1,start_position, length)
|
SELECT SUBSTRING('www.sql-programmers.com',5,15)
Output:
sql-programmers
|
| LEFT |
This function accepts an input string and returns the specified length string from the left hand side of the input string.
Syntax
LEFT(string1, length)
|
SELECT LEFT('Hello World',5)
Output:
Hello
|
| RIGHT |
This function accepts an input string and returns the specified length string from the right hand side of the input string.
Syntax
RIGHT(string1, length)
|
SELECT RIGHT('Hello World',5)
Output:
World
|
| LEN |
The LEN returns the length of a string. i.e., returns the count of characters in a string. This function ignores the blank spaces, if any, at the end of the input string.
Syntax
LEN(string1)
|
SELECT LEN('Hello World')
Output:
11
SELECT LEN('Hello World ')
Output:
11
|
| REVERSE |
It returns the reverse of the input string.
Syntax
REVERSE(string1)
|
SELECT REVERSE('Hello World')
Output:
dlroW olleH
|
| LOWER |
It converts all upper case letters in a given input string to lower case letters
Syntax
LOWER(string1)
|
SELECT LOWER('Hello World')
Output:
hello world
|
| UPPER |
It converts all lower case letters in a given input string to upper case letters.
Syntax
UPPER(string1)
|
SELECT UPPER('Hello World')
Output:
HELLO WORLD
|
| LTRIM |
The LTRIM function removes the blank spaces at the beginning of the input string i.e., the leading blanks are removed.
Syntax
LTRIM(string1)
|
SELECT LTRIM(' Hello World')
Output:
Hello World
|
| RTRIM |
The RTRIM function removes the blank spaces at the end of the input string i.e., the trailing blanks are removed.
Syntax
RTRIM(string1)
|
SELECT RTRIM('Hello World ')
Output:
Hello World
|