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.

DOT NET ADDA

interested in solving the problems based on technologies like Amazon AWS ,Google Cloud, Azure and Dot related technology like asp.net, C#, asp.net core API, swagger, react js,Jquery ,javascripts, bootstrap, css,html, ms sql,IIS,WPF ,WCF,Firebase,RDLC Report etc..

إرسال تعليق (0)
أحدث أقدم