CODEWITHSUNDEEP
sqlsql-server
Kenshin H
Kenshin H

Reputation: 71

SQL: Split string to join tables UPDATED

I have two tables that look like this.

Table Applicant

LName    FName    MI
Adamson  Leon     R
Alfano   Vincent
Bost     Darrod   D

Table ApplicantScore

ID    Name               Score
1     Adamson, Leon R.    89
2     Alfano, Vincent     99
3     Bost, Darrod D.     81
4     Smith, John         90
5     Chen, Lao           90
...

Any name that has an MI ends with a period.

I need to use the data in the table Applicant to retrieve the information from the table ApplicantScore. So, in the end, it will look like,

ID    Name               Score
1     Adamson, Leon R    89
2     Alfano, Vincent    99
3     Bost, Darrod D     81

I am using this SQL, but I am not getting what I need.

select a.ID, a.name, a.score
from Applicant a
left join ApplicantScore b 
on (REPLACE(b.Name, ' ', '') = REPLACE(a.LName + ',' + a.FName + 
ISNULL(a.MI, ''), ' ', ''));

The result I get is:

ID    Name                Score
NULL  NULL                NULL
2     Alfano, Vincent     99
NULL  NULL                NULL

Help, please?

Upvotes: 1

Views: 108

Answers (2)

S3S
S3S

Reputation: 25132

As suggested in my comments...

declare @Applicant table (LName varchar(64), FName varchar(64), MI char(1))
declare @ApplicantScore table (ID int identity (1,1), [Name] varchar(256), Score int)

insert into @Applicant
values
('Adamson','Leon','R'),
('Alfano','Vincent',null),
('Bost','Darrod','D')

insert into @ApplicantScore
values
('Adamson, Leon R',89),
('Alfano, Vincent',99),
('Bost, Darrod D',81),
('Smith, John',90),
('Chen, Lao',90)

select b.ID, b.name, b.Score
from @Applicant a
INNER JOIN @ApplicantScore b on 
replace(b.Name,' ','') = (RTRIM(a.LName) + ',' + RTRIM(a.FName) + ISNULL(a.MI,''))

Upvotes: 1

vc 74
vc 74

Reputation: 38179

select b.id, b.name, b.score
from Applicant a
inner join ApplicantScore b
on (b.Name = a.LName | ', ' | a.FName | 
    CASE WHEN a.MI IS NULL THEN ''
         ELSE ' ' | a.MI
         END));

Or removing spaces in both ends as suggested by @scsimon

select b.id, b.name, b.score
from Applicant a
inner join ApplicantScore b
on (REPLACE(b.Name, ' ', '') = 
    REPLACE(a.LName | ',' | a.FName | ISNULL(a.MI, ''), ' ', ''));

Upvotes: 4

Related Questions