By Bob Blackburn

This post will provide an introduction to the CHARINDEX function. Most SQL users are familiar with using LIKE to determine if a string contains another string. But, when comparing columns another method is needed.

LIKE Example

Select *

  From Customer

 Where FullName like '%SMITH%'

This will find people with the name of Smith including Smith Jr. Etc.

However, when you are trying to match two tables on substrings, it does not appear to be as straight forward. I have seen people use multiple WHERE conditions trying to see if the substring is at the beginning, middle or end of the lookup string. There is already a built in function CHARINDEX that can be used to keep your code clean.

CHARINDEX will give you the start position of the substring if it exists in the lookup string. For this example we only care if it is found. So, if the result is greater than zero, the string is found.

Our demo will find the foreign key to a State table from a title field.

   Declare @State table (Stateid int, StateCode char(2), StateName varchar(50));

   insert into @state (Stateid, StateCode, StateName) values (1, 'NY', 'NEW YORK');

   insert into @state (Stateid, StateCode, StateName) values (2, 'PA', 'PENNSYLVANIA');

   insert into @state (Stateid, StateCode, StateName) values (3, 'NJ', 'NEW JERSEY');

   Declare @Title table (Name varchar(50));

   insert into @Title (Name) values ('NEW YORK STATE');

   insert into @Title (Name) values ('THE STATE OF NEW YORK');

   insert into @Title (Name) values ('PENNSYLVANIA');

   insert into @Title (Name) values ('COMMONWEALTH OF PENNSYLVANIA');

   Select *

     from @Title t

        cross join @State s

        where charindex(StateName, t.Name)  > 0

Result