November 14, 2012 at 10:20 am
How can I join table A to table B where everything to the left of '-' in A.SSORID = B.policy and everything to the right of the '-' in A.SSORID = B.term?
A.SSORID
12456-7
B.policy
12456
B.term
7
November 14, 2012 at 12:05 pm
Hi and welcome to SSC.
The reason you are having such a hard time with this is because you have broken the rule of normalization where we only store a single value per field. If at all possible you should break SSORID into two columns to represent the individual data points.
If fixing the problem is not possible and you just have to plug your nose and more forward you can do something like the query below. Please note that if you have large tables you can forget about decent performance. This will produce a full scan and no amount of indexing is going to make this any faster.
Please notice that I posted ddl along with sample data to make evaluating this a lot easier. That is something you should do in future posts.
;with TableA (SSORID) as
(
select '12456-7'
)
, TableB(policy, term) as
(
select '12456', '7'
)
select *
from TableA a
join TableB b on LEFT(a.SSORID, charindex('-', a.SSORID) - 1) = b.policy
and RIGHT(a.SSORID, len(a.SSORID) - charindex('-', a.SSORID)) = b.term
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2012 at 3:29 pm
It works, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply