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))
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))
Hi Venkat,
ReplyDeleteI 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
Why we cant use ALIAS name in Group By Clause ?
ReplyDeleteThanks
wasim.add@gmail.com
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.
DeleteThanks!
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...
Deletecreate 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...
In Execution order of SELECT statement, ....GROUP BY comes first rather than SELECT
ReplyDeleteHi, with out using charindex and substring concepts How can we get the desired output.
ReplyDeleteDomain Name can also be Get as
ReplyDeleteRIGHT(@email,len(@Email)-charindex('@',@Email))
Hi
ReplyDeleteHow 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.
in mysql you have to use POSITION() instead of Charindex.
ReplyDelete