You have them the wrong way around:
select charindex('bcd','abcde')
gives 2
select charindex('abcde','bcd')
gives 0
Rob
Rob Farley
2009-08-26 00:39:48
A:
flipped around my columns and now I get no results, probably because of the '01','02','03' which are the first three characters (including the space) before the start of the last name
Matt
2009-08-26 00:44:30
Oh, sorry... I had read that as being a separate field. You could use: charindex(stuff(t2.column,1,charindex(' ',t2.column),''),t1.column) - but I would recommend storing your data differently, so that you can help it with indexes.
Rob Farley
2009-08-26 00:58:06
A:
The first parameter of CHARINDEX (or PATINDEX) must be a substring of the second parameter. Neither function is smart enough to match on a specific portion of the substring. 'Rice/Bennet' can't be found in '02 Rice'. Examples:
CHARINDEX('Miller', '01 Miller') = 4
CHARINDEX('Grant', '03 Grant') = 4
CHARINDEX('Rice/Bennet', '02 Rice') = 0
In order to make the query work, you'd need to construct an inline view where you parse the Table1.Column value to deal with these situations. CHARINDEX/PATINDEX tells us you're using SQL Server - if it is 2005+, you can use Common Table Expressions (CTEs).
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
Mind that in the example, 'Bennet' wouldn't ever be used to check for a corresponding entry in Table2.
OMG Ponies
2009-08-26 00:56:13
And unfortunately I am working on migrating these "2000" Db's to one SQL 2005/2008 database, depending on what I can get my boss to buy.
Matt
2009-08-26 00:59:20
Nothing wrong with either, just glad I provided the inline view example rather than a CTE =)
OMG Ponies
2009-08-26 01:00:36
I am having a hard time getting that into my already crazy join, do you think I could just do somethign in the where clause based on my question updat ( using a reg expression on table2.column and just charindex that value on the full last name in table1.column?
Matt
2009-08-26 01:17:06
Sorry, no. Update the question with what is complicating the join, I'll take a shot at it.
OMG Ponies
2009-08-26 01:32:18
I'm just getting way to many records back, the join is creating more records than there should be.
Matt
2009-08-26 02:03:06
I'm not sure how much help we'll be here if you don't provide more detail. You'd need a function that supports regex in order to filter Table2.column values, which does exist until you're using SS 2008 as I understand.
OMG Ponies
2009-08-26 02:15:13
I see what you mean by the regex. I'll play around with your solution for a while. Thanks for your help so far!
Matt
2009-08-26 02:17:04
+2
A:
Based on the update to your question if you want to trim off the first few characters including the space in the 2nd column and compare that to the 1st column like you described then this is what you can stick down in your WHERE clause.
(CHARINDEX(RIGHT(table2.column, len(table2.column)-CHARINDEX(' ', table2.column)), table1.column) > 0)
Breadtruck
2009-08-26 14:15:14