How to get Count from CHARINDEX Function

How to use the CHARINDEX Function

The CHARINDEX function returns the starting position of a character, or a string of characters within another character string. The CHARINDEX function is called using the following format:

CHARINDEX ( expression1 , expression2 [ , start_location ] )

Where expression1 is the string of characters to be found in expression2, and start_location is the position where the CHARINDEX function will start looking for expression1 in expression2.

The CHARINDEX function returns an integer. The integer value returned is the position where the characters being search for are located within the string being searched. If the CHARINDEX does not find the characters you are searching for then the function returns a zero integer value. Let say we execute the following CHARINDEX function call:

 CHARINDEX('SQL', 'Microsoft SQL Server')

This function call will return the starting location of the character string "SQL", in the string "Microsoft SQL Server". In this case the CHARINDEX function will return the number 11, which as you can see is the starting position of "S" in string "Microsoft SQL Server".

Now say we have the following CHARINDEX Command:

 CHARINDEX('7.0', 'Microsoft SQL Server 2000')

In this example the CHARINDEX function will return zero, since the character string "7.0" cannot be found in the string "Microsoft SQL Server 2000". Let go through a couple of examples of how you might be able to use the CHARINDEX function to solve some actual T-SQL problems.

For the first example say you would like to display only the last name of the ContactName column, for the first 5 records in the Northwind database Customer table. Here are the first 5 records.

 ContactName
 ------------------------------ 
 Maria Anders
 Ana Trujillo
 Antonio Moreno
 Thomas Hardy
 Christina Berglund

As you can see, the CustomerName contains both the first and last name of the customer, where first and last name are separated by a single space. I will use the CHARINDEX function to identify the position of the space between the two names. This way we can used the position of the space to parse the ContactName so we can display only the last name portion of the column. Here is some T-SQL code to display only the last name for the first 5 records in the Northwind Customer table.

 select top 5 substring(ContactName,
                       charindex(' ',ContactName)+1 ,
                       len(ContactName)) as [Last Name]
       from Northwind.dbo.customers

Here is the output from this command:

 Last Name
 ------------------------------ 
 Anders
 Trujillo
 Moreno
 Hardy
 Berglund

The CHARINDEX function found the space between the First and Last Name, so that the substring function could split the ContactName, thus only the Last Name was displayed. I added 1 to the integer value that CHARINDEX returned, so the Last Name displayed did not start with a space.

For the second example, say you want to count all the records from a table where a given column contains a particular character string. The CHARINDEX function could be used to satisfy your request. To count of the Addresses in the Northwind.dbo.Customer table where the Address column contains either the word 'Road' or an abbreviation for road ('Rd'), your SELECT statement would look like this:

you can't do count(charindex(string)) would give syntax error

But to resolve error see this

 select count(*) from Northwind.dbo.Customers 
   where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)
 > 1