SQL Server Built-in Functions Examples

 

 

 

 

SQL Server Built-in Functions

These are the following  list of built-in String functions, DateTime functions, Numeric functions and conversion functions.

String Functions

SQL Server STUFF() function overview

The STUFF() function deletes a part of a string and then inserts a substring into the string, beginning at a specified position.

The following shows the syntax of the STUFF() function:

STUFF ( input_string , start_position , length , replace_with_substring)

Example: 

 SELECT  STUFF('Rakesh Gupta', 1 , 6, 'Suresh Kumar') result;

Syntax

SUBSTRING(string, start, length)

 

Example:

 SELECT SUBSTRING('SQL Example', 1, 3) AS ExtractString;

Syntax

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

 

Example: 

 SELECT CONCAT('SQL', ' is', ' RDMS');  

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

Syntax

STR(number, length, decimals)

 

SELECT STR(200); 

The PATINDEX() function returns the position of a pattern in a string.

If the pattern is not found, this function returns 0.

Syntax

PATINDEX(%pattern%, string)

 

Example

Return the position of a pattern in a string:

SELECT PATINDEX('%s%com%', 'dotnetadda.com'); 

 

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

Syntax

REPLACE(string, old_string, new_string)

 

Example

Replace "SQL" with "C#":

SELECT REPLACE('SQL TEST', 'SQL', 'C#');

 

 

ASCII	Returns the ASCII code value for the leftmost character of a character expression.
CHAR	Returns a character for an ASCII value.
CHARINDEX	Searches for one character expression within another character expression and returns the starting position of the first expression.
CONCAT	Concatenates two or more string values in an end to end manner and returns a single string.
LEFT	Returns a given number of characters from a character string starting from the left
LEN	Returns a specified number of characters from a character string.
LOWER	Converts a string to lower case.
LTRIM	Removes all the leading blanks from a character string.
NCHAR	Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
PATINDEX	Returns the starting position of the first occurrence of the pattern in a given string.
REPLACE	Replaces all occurrences of a specified string with another string value.
RIGHT	Returns the right part of a string with the specified number of characters.
RTRIM	Returns a string after truncating all trailing spaces.
SPACE	Returns a string of repeated spaces.
STR	Returns character data converted from numeric data. The character data is right justified, with a specified length and decimal precision.
STUFF	Inserts a string into another string. It deletes a specified length of characters from the first string at the start position and then inserts the second string into the first string at the start position.
SUBSTRING	Returns part of a character, binary, text, or image expression
UPPER	Converts a lowercase string to uppercase.