Below is the list of various functions with proper descriptions:
LEN ():-Returns the number of characters of the specified string expression, excluding trailing blanks. However, Len () counts the leading spaces as a character.
DATALENGTH ():- In cases, where we want the total length of a string expression including the trailing spaces we can use the datalength () function. The DATALENGTH of NULL is NULL.
In the below example, the Len()returns 15 whereas datalength returns 17. The actual length of the string (Bangalore City) is 14.
Select Len (' Bangalore City ') ---15
Select DataLength (' Bangalore City ') ---17
TRIM ():- To Remove the leading and trailing spaces from a string expression we can use the TRIM () functions. As the name suggests LTRIM will remove the leading spaces while RTRIM will remove the trailing spaces.
Select LEN (LTRIM (' Bangalore City')) ---14
Select LEN (RTRIM (' Bangalore City ')) ---15
Select LEN (LTRIM (RTRIM (' Bangalore City '))) ---14
Substring function is used to get the required\desired string from a string expression. The syntax is: Substring (
Select Substring ('Bangalore City', StartIndex, Length)
Select Substring (Column_name, StartIndex, Length) AS ShortName from Table_Name
RIGHT () and LEFT ():-
Right and Left are 2 string functions used to get the required\desired string from a string expression from 2 sides i.e. Right and Left side respectively.
Syntax: Right\Left (
Select Right ('Bangalore City ', 6)
Select Left ('Bangalore City ', 10)
The ISNULL function is used to return the specified value if the value is NULL.
Select ISNULL (Column_name, 0) from Table_Name.
In the above example all the null values in the column will return as zero.
As the name suggests, Replace () is used to replace all occurrences of a specified string value with another string value.
REPLACE (Column_name, 'text to replace', 'replace with text') from Table_Name where
STUFF also replaces one section of a string with another, but does so based on the starting position and length of the section to replace.
Syntax: STUFF (
Select STUFF ('Bangalore City', 4, 6,'XXX')
Output will be 'BanXXXlore City'.
And if the length of the section is more than the no. of characters in the 'replace with text' then the characters are skipped till that length.
select STUFF ('Bangalore City', 4, 6,'XXX')
Output will be 'BanXXX City'.
REPLICATE is used to repeat a string for specified number of times.
Syntax: Replicate (
SELECT ('1234567') +REPLICATE ('0', 10 - LEN (1234567'))
Output will be '1234567000'.
This function can be used to add leading\trailing zeroes to a string. It can also be used to encode values of a column. E.g.
SELECT REPLICATE ('*', LEN ('1234567'))
Output will be '*******'
As the name suggests Convert function is used to change the datatype of the expression\column value.
select Convert (varchar (20), 123456) AS Value
Now the datatype of '123456' is varchar instead of numeric or Int.
select Convert (Date, GetDate ()) AS Date
More articles: SQL function