Problem Involving String Data and Concatenation

[sjdig]

Hello all.

I am working on building out a report to identify Names in our Database that are currently stored incorrectly.

Good Data Example (no middle initial):
Name: John Doe
First Name: John
Middle Initial: ''(empty space)
Last Name: Doe

Bad Data Example (no middle initial):
Name: Jill Doe
First Name: Doe
Middle Initial: ''(empty space)
Last Name: Jill

We retrieve Name using the Last Name followed by a comma and then the First Name, so you could see how this could be an issue.

As there was some inconsistencies, I made two queries, one where middle name exists and the other where it does not.

Name With Middle Initial Query:

Query Calculation of Name Comparison: [First Name] ||' '|| [Middle Initial] ||' '|| [Last Name]
Query Calculation of Check: case when [Name] <> [Name Comparison] then 'Fail' else 'Pass' end


Name With No Middle Initial Query:

Query Calculation of Name Comparison: [First Name] ||' '|| [Last Name]
Query Calculation of Check: case when [Name] <> [Name Comparison] then 'Fail' else 'Pass' end

This works okay, but there are some issues when suffixes such as JR, SR, DMD, etc. These are stored on the Name item itself but nowhere in First Name, Middle Initial, or Last Name. I know I can filter down using Right([Name], 3) not in (' JR', ' SR', 'DMD'), but was hoping there might be a more elegant solution than that. I left the space on JR and SR in case we had a name actually end with those characters for some reason.

In addition, if the middle name is able to be entered in the 40 character string limit of the Name field, it usually is as below. 

For example:
Name: Bob Bill Doe
First Name: Bob
Middle Initial: B
Last Name: Doe

This creates a fail when it's not a true fail in my Name With Middle Initial Query.

Is there some sort of way to take my Name field and basically convert it when the middle name is stored to only return the first letter?

I was thinking maybe using substring in some fashion, but wasn't sure of the best way to go about it. I was unaware if there was maybe a way to return the value until the first space from the main Name field or something similar. As it could be varying lengths every time, I didn't know if that might also pose a problem as well.

If it helps, the field values all have the following set lengths that I usually rtrim as they pad out with empty characters otherwise.

Name: 40
First Name: 15
Middle Initial: 1
Last Name: 25

Thanks in advance for any suggestions or advice. It is most appreciated.
sjdig

[CogFanGal]

Hi sjdig,
I am confused on what the objective is by your example.  Neither of the 2 test records have a middle initial, but I do see that the 2nd test record has the first and last name field values flip-flopped (First Name value is in the Last Name field, and vice versa).   What is the objective?  Is it to display the name correctly on a report (i.e. Last, First M.) regardless if the data is stored correctly like in the 1st record or reversed like in the 2nd record (and while also keeping any suffix like Sr., Jr., etc.)?

[sjdig]

Hi, it's essentially being used as a data dump to a CSV to run an update on stored keyword records within our imaging system and then implement the use of autofill records going forward in the same imaging system. Cognos is pulling the data from our core database that I'll be using on the back end for this process. I'm trying to identify bad data to be corrected so that it can be fixed prior to updating the stored items in our imaging solution and the output is as should be expected.

For example, if I ran the process with Jill Doe as in my example, my imaging system would search for Jill Doe's SSN and then update the new keyword records to be First Name: Doe and Last Name: Jill. This is a problem as that is not how a user would expect to retrieve that data.

I know we have quite a bit of legacy data like this in our system that needs correcting. However, the data I'll be putting into the imaging solution would need to include even outdated items as there will be documents in that system that are not beyond their retention scope yet and need to be updated with these new values.

I believe I may have found a way to get what I need using charindex. It did involve building out quite a number of ad-hoc data items though. If I have more time later, I'll post how I went about it in case someone in the future has this issue.

sjdig

[CogFanGal]

Thanks for the update sjdig.