I have two tables I am wanting to inner join. The column of one is Ordind000.procplanid . This has the job, the J000012345, and suffix, the .00001 both in this one column. But in the Job table that i need to join it to both the job and the suffix are separate columns. My guess is i need to use the replace command and write it to a temp table, but i am hoping for a much easier solution. I will also need to strip the decimal at the beginning of the job and suffix numbers in the proclanid columns i think. ![alt text] : /storage/temp/3343-image-5.png
It is possible to have functions and conversions in `JOIN` conditions; however, it's not necessarily a good way to go. However, a quick & dirty example to show the basics: -- just setting up a couple of tables with some really inconsistent data... DECLARE @JobPlan TABLE ( ProcPlanID VARCHAR(30) , OtherData VARCHAR(50) ); DECLARE @Job TABLE ( JobID VARCHAR(20) , Suffix INT , OtherJobData VARCHAR(50) ); INSERT INTO @JobPlan VALUES ( 'J007.001', 'Bob Holness' ), ( 'J007.002', 'Sean Connery' ), ( 'J008.001', 'Sean Bean' ); INSERT INTO @Job VALUES ( 'J007', 1, 'Casino Royale' ), ( 'J007', 2, 'The Man with the Golden Gun' ), ( 'J008', 1, 'Goldfinger' ); -- This is where we're doing a join similar to the one you want SELECT * FROM @JobPlan jp LEFT JOIN @Job j ON j.JobID = LEFT(jp.ProcPlanID, CHARINDEX('.', jp.ProcPlanID) - 1) AND j.Suffix = CONVERT(INTEGER, RIGHT(jp.ProcPlanID, 3)); As you can see in that JOIN statement, we're taking the left few characters (based on the position of the '.' - you might want to use the position of a space); the other part of the join is me knowing that the suffix is restricted to three characters, so I'm just grabbing those. If you can't guarantee that, then a nest of REVERSEs and a CHARINDEX might help, but that's another query for another day.