SQL Server - In Built Functions


SQL Server provides very large range of in built functions.
Function must return value.

Below is some important list of in built functions.


Function Function Description
LEN Return the length of string
CHARINDEX Return the position of string in another string
SUBSTRING Return the sub string from string.
CONCAT Concat tow or more string.
FORMAT Format string or date in specified format.
LEFT Return the nth number of character from left side.
RIGHT Return the nth number of character from right side.
UPPER Return the given string in upper case format.
LOWER Return the given string in lower case format.
REVERSE Revers the given string.
REPLACE Replace sub string in string.
LTRIM Remove extra spaces from left side of string.
RTRIM Remove extra spaces from right side of string.
ISNUMERIC Function check the given value is numeric or not, it return bit value that is 0 for false and 1 for true.
CAST Convert given value from one data type to another.
CONVERT Convert given value from one data type to another.
ISNULL It check that given value is null then show provided value.
NULLIF It return NULL if given both values are same.
DAY Return the day number from give date time, It return int value.
MONTH Return the month number from give date time, It return int value.
YEAR Return the year from give date time, It return int value.
DATEADD It add date part (second, minute, hours, day, month, year) in another date.

LEN function example.

Return the number of character in given string.

Example

DECLARE @String VARCHAR (100) = 'Dot Net Guru'
SELECT LEN(@String) AS 'Out Put'

Output

SQL Server LEN function example


Note : It consider blank space as one character.


CHARINDEX function example.

Return the position of string in another string. We also user this function to find one string in another string in SQL server.

Example

DECLARE @String VARCHAR (100) = 'Dot Net Guru'
SELECT CHARINDEX('Net' , @String) AS 'Out Put'

Output

SQL Server CHARINDEX function example


Note : If first string not found in another string then CHARINDEX function return 0 value.


SUBSTRING function example.

Return the sub string from string. It also help to get sub string from another string.

Example

DECLARE @String VARCHAR (100) = 'Dot Net Guru'
SELECT SUBSTRING(@String,5,3) AS 'Out Put'

Output

SQL Server SUBSTRING function example


CONCAT function example.

Concat (combine) tow or more string.

Example

SELECT CONCAT('Dot Net' , ' Guru') AS 'Out Put'

Output

CONCAT wo string in sql server


FORMAT function example.

Format string or date in specified format.

Example

--format number
SELECT FORMAT(10500 , '##,###') AS 'Out Put'

--format date
SELECT FORMAT(GETDATE() , 'dd (dddd) MMM yyyy') AS 'Out Put'

Output

FROMAT datetime and number example in sql server


LEFT and RIGHT function example.

Return the nth number of character from left and right side

Example

--LEFT
SELECT LEFT('Dot Net Guru', 7) AS 'Out Put'

--RIGHT
SELECT RIGHT('Dot Net Guru', 4) AS 'Out Put'

Output

get string from left or right side in sql server, get sub string from left/right side example in sql server


UPPER and LOWER function example.

Return the given string in upper case or lower case format.

Example

--UPPER Case
SELECT UPPER('Dot Net Guru') AS 'Out Put'

--LOWER Case
SELECT LOWER('Dot Net Guru') AS 'Out Put'

Output

convert string in lower / upper case format in sql server example


REVERSE function example.

Revers the given string.

Example

SELECT REVERSE('Dot Net Guru') AS 'Out Put'

Output

reverse string in sql server


REPLACE function example.

Replace sub string in string.

Example

SELECT REPLACE('Dot Net Guru',' ','*',) AS 'Out Put'

Output

replace string in sql server, replace one string from another in sql server


Note : In above example replace blank space with * character.


LTRIM and RTRIM function example.

LTRIM remove extra spaces from left side of string.
RTRIM remove extra spaces from right side of string.

Example

--LTRIM : remove extra space from left side of string
SELECT LTRIM('       Dot Net Guru      ') AS 'Out Put'

--RTRIM : remove extra space from right side of string
SELECT RTRIM('       Dot Net Guru      ') AS 'Out Put'

Output

remove extra space from string in sql server, LTRIM and RTRIM example in sql server


ISNUMERIC function example.

Function check the given value is numeric or not, it return bit value that is 0 for false and 1 for true.

Example

SELECT ISNUMERIC(34343) AS 'Out Put'
SELECT ISNUMERIC('String') AS 'Out Put'

Output

replace string in sql server


CAST and CONVERT function example.

Convert given value from one data type to another.

Example

--CAST
SELECT CAST(12393 AS DECIMAL(18,2)) AS 'Out Put'
--CONVERT
SELECT CONVERT(DECIMAL(18,2) , 12393) AS 'Out Put'

Output

convert / cast value in sql server. Convert value from one data type to another in sql server


ISNULL example.

It check that given value is null then show provided value. Click on ISNULL to find more details.


NULLIF example.

It return NULL if given both values are same. Click on NULLIF to find more details.


DAY, MONTH, YEAR function example.

To find out the day, month or year from provided date time. It return the numeric value as output.

Example

--Date format is : year-month-date
--DAY
SELECT DAY('2020-08-09') AS 'Out Put'

--MONTH
SELECT MONTH('2020-08-09') AS 'Out Put'

--YEAR
SELECT YEAR('2020-08-09') AS 'Out Put'

Output

get day, month, year from date in sql server


DATEADD function example.

It add date part (second, minute, hours, day, month, year) in another date.

Example

SELECT DATEADD(DAY, 5, '2020-08-09') AS 'Out Put'

SELECT DATEADD(MONTH, 2, '2020-08-09') AS 'Out Put'

SELECT DATEADD(YEAR, 1, '2020-08-09') AS 'Out Put'

Output

Add day, month, year in date in sql server




 



WebNetGuru provides the developer to learn and understand the technical concepts. We try to provide solution in easy and step by step way. We continuously keep updating content and topics, so we cannot warrant full correctness of all contents.
WebNetGuru team will try to keep updating new technology tutorial.
Current Tutorials
Upcoming Tutorials
  • ASP.NET MVC
  • Angular JS
  • Mongo DB
CONTACT US
  • contact@webnetguru.com
WORK WITH US
© Copyright 2020 WebNetGuru.com