SQL server functions and build-in functions

These all are the function SQL Server which helps the user manipulate the SQL QUERY. You can learn string, numeric, date, conversion, and some advanced functions in SQL Server.

SQL server functions and built-in functions

ASCII() Function : - 

ASCII() function in SQL gives the value for the specified character.

Syntax : - 

ASCII(character)

CHAR() Function : - 

CHAR() function in SQL reads the character based on the ASCII code.

Syntax : - 

CHAR(code)

CHARINDEX() Function : - 

CHARINDEX() function in SQL search for the substring in a string and returns the position. If any substring not founded then returns 0.

Syntax : - 

CHARINDEX(substring, 
  string, start)

  SQL server CONCAT() function : -

CONCAT() method in SQL use to add two or more string together.

Syntax : - 

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

SQL Server Concat With + : -

The + operator also works like the Concat() method for adding two or more strings together.

Syntax : - 

string1 + string2 + string_n

CONCAT_WS() Function in SQL server: - 

CONCAT_WS() function in SQL use to add two or more string together with a separator.

Syntax : - 

CONCAT_WS(separator, string1,string2, ...., string_n)

DATALENGTH() Function in SQL server: - 

DATALENGTH() function in SQL gives the number of bytes used to represent an expression.

Syntax : - 

DATALENGTH(expression)

 DIFFERENCE() Function in SQL server: - 

 DIFFERENCE() for SQL compares two SOUNDEX( four-character code) values and returns an integer. Integer value indicates the match for the two SOUNDEX values, from 0 to 4. compares two SOUNDEX values.

Syntax : - 

DIFFERENCE(expression, expression)

 FORMAT() Function in SQL server: - 

 FORMAT() for SQL function formats a value with the specified format.

Syntax : - 

FORMAT(value, format, culture)

LEFT() Function in SQL server: - 

 LEFT() method in SQL extracts a number of characters from a string that is starting from left.

Syntax : - 

LEFT(string, number_of_chars)

LEN() function of SQL : - 

 LEN() function from the SQL server returns the length of a string but the space form the string is not included.

Syntax : - 

LEN(string)

LOWER() function of SQL : - 

LOWER() function in SQL returns converts a string to lower-case.

Syntax : - 

LOWER(text)

LTRIM() function: - 

LTRIM() function used to removes leading spaces from a string.

Syntax : - 

LTRIM(string)

NCHAR() function from SQL: - 

NCHAR() function of SQL return the Unicode character on the basis of number code.

Syntax : - 

NCHAR(number_code)

PATINDEX() function from SQL: - 

PATINDEX() function gives you the position (address) of a pattern in a string. If the pattern is not found then return zero.

Syntax : - 

PATINDEX(%pattern%, string)

    % - Match any string of any length (including 0 lengths)
    _ - Match an only single character
    [] - Match any characters within the brackets, e.g. [xyz]
    [^] - Match any character, which is not in the brackets, e.g. [^xyz].

QUOTENAME() function from SQL server: - 

QUOTENAME() function in SQL returns the Unicode string with delimiters added for making that string a valid SQL Server delimited identifier.

Syntax : - 

QUOTENAME(string, quote_char)

REPLACE() in SQL: - 

REPLACE() method in SQLreplace all the substring within a string, with a new substring and search is case-insensitive.

Syntax : - 

REPLACE(string, old_string, new_string)

REPLICATE() in SQL: - 

REPLICATE() Function in SQL repeats a string a specified number of times.

Syntax : - 

REPLICATE(string, integer)

REVERSE() in SQL: - 

REVERSE() Function in SQL gives you a reverse string as the result.

Syntax : - 

REVERSE(string)

REVERSE() in SQL: - 

REVERSE() Function in SQL gives you a reverse string as the result.

Syntax : - 

REVERSE(string)

RTRIM() in SQL: - 

RTRIM() in SQL removes all space characters from the right-hand side of a string.

Syntax : - 

RTRIM( string )

SOUNDEX() server function in SQL: - 

SOUNDEX() in SQL find a four-character code so that it evaluates the similarity of two expressions.

Syntax : - 

SOUNDEX(expression)

SPACE() server function in SQL: - 

SPACE() method in SQL returns a string with a specified number of spaces.

Syntax : - 

SPACE(number)

STR() server function in SQL: - 

STR() in SQL function returns a string that represents a number.

Syntax : - 

STR(number, length, decimals)

STUFF() server function in SQL: - 

STUFF() function allow you to edit the string with deletes a part of a string and then inserts another part into the string starting point specified by the user.

Syntax : - 

STUFF(string, start, length, new_string)

SUBSTRING() in SQL: - 

SUBSTRING() is a part of the string and this function extracts some characters from a string.

Syntax : - 

SUBSTRING(string, start, length)

SUBSTRING() in SQL: -

SUBSTRING() is a part of the string and this function extracts some characters from a string.

Syntax : - 

SUBSTRING(string, start, length)

TRIM() in SQL: -

TRIM() method in SQL removes the space character OR other specified characters from the start or end of a string. There are two more kinds of method to trim LTRIM() and RTRIM() functions.

Syntax : - 

TRIM([characters FROM ]string)

TRIM() in SQL: -

TRIM() method in SQL removes the space character OR other specified characters from the start or end of a string. There are two more kinds of method to trim LTRIM() and RTRIM() functions.

Syntax : - 

TRIM([characters FROM ]string)

UNICODE in SQL: -

UNICODE() in SQL gives the first character from the first character of the input expression.

Syntax : - 

UNICODE(character_expression)

UNICODE in SQL: -

UPPER() function in SQL helps you to changes a string to the upper-case.

Syntax : - 

UPPER(text)

Read More