LEFT, RIGHT, CHARINDEX and SUBSTRING functions - Part 23

In this video we will learn about the commonly used built-in string functions in SQL server and finally, a real time example of using string functions. Please watch the following videos, before continuing with this video.
Part 11 – Group By
Part 22 – Built in string functions







LEFT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the left hand side of the given character expression.

Example: Select LEFT('ABCDE', 3)
Output: ABC

RIGHT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the right hand side of the given character expression.

Example: Select RIGHT('ABCDE', 3)
Output: CDE

CHARINDEX('Expression_To_Find', 'Expression_To_Search', 'Start_Location') - Returns the starting position of the specified expression in a character string. Start_Location parameter is optional.

Example: In this example, we get the starting position of '@' character in the email string 'sara@aaa.com'. 
Select CHARINDEX('@','sara@aaa.com',1)
Output: 5

SUBSTRING('Expression', 'Start', 'Length') - As the name, suggests, this function returns substring (part of the string), from the given expression. You specify the starting location using the 'start' parameter and the number of characters in the substring using 'Length' parameter. All the 3 parameters are mandatory.

Example: Display just the domain part of the given email 'John@bbb.com'.
Select SUBSTRING('John@bbb.com',6, 7)
Output: bbb.com

In the above example, we have hardcoded the starting position and the length parameters. Instead of hardcoding we can dynamically retrieve them using CHARINDEX() and LEN() string functions as shown below.

Example:
Select SUBSTRING('John@bbb.com',(CHARINDEX('@', 'John@bbb.com') + 1), (LEN('John@bbb.com') - CHARINDEX('@','John@bbb.com')))
Output: bbb.com

Real time example, where we can use LEN(), CHARINDEX() and SUBSTRING() functions. Let us assume we have table as shown below. 


Write a query to find out total number of emails, by domain. The result of the query should be as shown below.


Query
Select SUBSTRING(Email, CHARINDEX('@', Email) + 1,
LEN(Email) - CHARINDEX('@', Email)) as EmailDomain,
COUNT(Email) as Total
from tblEmployee
Group By SUBSTRING(Email, CHARINDEX('@', Email) + 1,
LEN(Email) - CHARINDEX('@', Email))

9 comments:

  1. Hi Venkat,
    I have 2005 version of SQL, each time i execute the query that gets the Email domain at the end of the 23rd session I get an error ("Email" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.) so i try to set the database compatibility mode to 90 or any other value after setting the database to the single mode but i get another error incorrect syntax
    can you please advise if the query is executable on the SQL 2005 Version

    ReplyDelete
  2. Why we cant use ALIAS name in Group By Clause ?

    Thanks
    wasim.add@gmail.com

    ReplyDelete
    Replies
    1. Hello, We cannot use alias name because it will give error invalid column name. As we know that alias name is something we just use for giving header text of the column while displaying result as more readable or for our convenient .. Hope you got this.

      Thanks!

      Delete
    2. Hi u can use the Alias Name .. But before that u should create a view .. then from the View u can take alias name (Domain) and then u can use it in the Group by clause .. as shown in the Below code...

      create view vw_DomainCount
      as
      select substring(Email,CharIndex('@',Email)+1,Len(Email)-Charindex('@',Email))
      as Domain
      from tblStudents

      select Domain,Count(*) as Total from vw_DomainCount
      group by Domain

      I hope u got the solution...

      Delete
  3. In Execution order of SELECT statement, ....GROUP BY comes first rather than SELECT

    ReplyDelete
  4. Hi, with out using charindex and substring concepts How can we get the desired output.

    ReplyDelete
  5. Domain Name can also be Get as

    RIGHT(@email,len(@Email)-charindex('@',@Email))

    ReplyDelete
  6. Hi
    How we can get the first characters in Substring by giving parameter in charindex and len
    Example : Substring('Hello@gmail.com' , Charindex , len)
    pls give me the correct charindex and len of the above expression.

    ReplyDelete
  7. in mysql you have to use POSITION() instead of Charindex.

    ReplyDelete

It would be great if you can help share these free resources