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
Output
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
Output
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
Output
CONCAT function example.
Concat (combine) tow or more string.
Example
Output
FORMAT function example.
Format string or date in specified format.
Example
Output
LEFT and RIGHT function example.
Return the nth number of character from left and right side
Example
Output
UPPER and LOWER function example.
Return the given string in upper case or lower case format.
Example
Output
REVERSE function example.
Revers the given string.
Example
Output
REPLACE function example.
Replace sub string in string.
Example
Output
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
Output
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
Output
CAST and CONVERT function example.
Convert given value from one data type to another.
Example
Output
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
Output
DATEADD function example.
It add date part (second, minute, hours, day, month, year) in another date.
Example
Output