SQL Function for String values

This article gives a sneak peek into the various SQL functions used for formatting string\varchar values.I hope this article will be useful to you as you can find all the string functions at one place with proper descriptions and examples.

Hi Friends,

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.
For e.g.

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 (, StartIndex, Length)

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 (, Length)
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 (, 'text to replace', 'replace with text')
REPLACE (Column_name, 'text to replace', 'replace with text') from Table_Name where

STUFF ():-
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 (, StartIndex, Length, 'replace with text')

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.
For Example,
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 (, n no. of times)
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


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: