SQL: Split string to join tables UPDATED

Question

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?

If possible you should fix that schema and store the applicant's ID rather than (a concatenation of) their name in the score table. — sticky bit
– sticky bit, Commented Mar 29, 2019 at 14:31
Why are you putting the % at the beginning? Name and LName should begin the same way. Why are you putting the commas at the beginning and end? — Karlomanio
– Karlomanio, Commented Mar 29, 2019 at 14:34
Using the SQL Server 2012. And I wish I could fix the schema, but I cannot... Just a lowly person, trying to do the given task. — Kenshin H
– Kenshin H, Commented Mar 29, 2019 at 14:36

vc 74 · Accepted Answer · 2019-03-29 15:44:39Z

4

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, ''), ' ', ''));

edited Mar 29, 2019 at 15:44

answered Mar 29, 2019 at 14:33

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2019-03-29 16:26:39Z

1

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,''))

answered Mar 29, 2019 at 16:26

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

SQL: Split string to join tables UPDATED

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related