Geek Answers Handbook

Help understand SQL connectivity using PATINDEX or CHARINDEX

Work on combining two outdated database systems into a new database, where I can avoid such stupid problems as what you see below. I am trying to make a join on two tables where the text data is different, and this usually works, unless the actual full last name was used in another table. There are other columns that are part of the join, but I still need to do the join in these two columns in order to get the correct results.

  Table1.Column Table2.Column
 Miller 01 Miller
 Grant 1-3 Grant
 Rice / Bennet 2A Rice

So (CHARINDEX (table1.column, Table2.Column)> 0) expet works for when the last name is slightly different from Rice / Bennet <> 02 Rice.

What is the best way to handle this? generally speaking, this type of senario is the only inhibitory one.

UPDATE: I thought it might be easier to replace any numbers and characters to the first place in table2.column and use this for table1.column ?? In previous years, I discovered that the first pair of characters can be numbers or letters, but there was a place before the last name

+4
source share
3 answers

Based on an update of your question, if you want to trim the first few characters, including the space in the second column, and compare them with the 1st column, as you described, this is what you can stick with in your WHERE clause.

(CHARINDEX (RIGHT (table2.column, len (table2.column) -CHARINDEX ('', table2.column), table1.column)> 0)

+2
source

You have them wrong:

select charindex('bcd','abcde')

gives 2

 select charindex('abcde','bcd')

gives 0

Rob

0
source

The first parameter, CHARINDEX (or PATINDEX), must be a substring of the second parameter. None of the functions are smart enough to match a specific part of a substring. Rice / Bennet cannot be found in Rice 02. Examples:

 CHARINDEX('Miller', '01 Miller') = 4 CHARINDEX('Grant', '03 Grant') = 4 CHARINDEX('Rice/Bennet', '02 Rice') = 0

To make the query work, you will need to build an inline view in which you will parse the value of Table1.Column to deal with these situations. CHARINDEX / PATINDEX reports that you are using SQL Server - if it is 2005+, you can use Common Table Expressions (CTE).

 JOIN (SELECT CASE WHEN CHARINDEX('/', t.column) > 0 THEN SUBSTRING(t.column, 0, CHARINDEX('/', t.column)-1) ELSE t.column END AS column FROM TABLE1 t) t1 ON CHARINDEX(t1.column, Table2.column) > 0

Remember that in the example, “Bennet” will never be used to check the corresponding entry in table2.

0
source

More articles:


All Articles