I was working with a database that was recording personnel data with first names in one record and last names in another with middle initials in another. Nothing unusual about that, but I wanted to try something different. The last names had already been stripped of all punctuation, hyphens, and dashes; this provided an opportunity to store them in one record. The pulled record could then be given to ColdFusion as a three item list, or let SQL do the work for a change.
In this example I am storing the names in a database table, in one record in the format "John.Q.Public". The plan is to show how to use SQL to divide this record into three pieces; NameFirst, NameMI, and NameLast. The trick is using a character that will divide the three names into non-repeating ranges. Since all punctuation was already removed from this data I used ".", but you could just as easily use the pipe "|" character or whatever works for you.
We use CHARINDEX to determine the position of the "." marks and get the number that represents the place it occurs in the record. This is variable because everyone's names are not the same length. SQL does not have great list tools like ColdFusion, so we have to use positional manipulation. To get the last name, we will use CHARINDEX again, but by reversing the record we can get the position of the last name. We then use LEFT and RIGHT to set the name ranges based on the values returned by CHARINDEX. The middle initial is a little more tricky. Easiest way is to PATINDEX the two decimal points surrounding the middle inital and then use the SUBSTRING to pull out one letter.
DECLARE @SQL VARCHAR(255);
SET @SQL='John.Q.Public';
SELECT @SQL AS NameConcat,
Left(@SQL, CHARINDEX('.', @SQL) - 1) AS NameFirst,
Right(@SQL, CHARINDEX('.', Reverse(@SQL)) - 1) AS NameLast,
SUBSTRING(@SQL, PATINDEX('%._.%', @SQL) + 1, 1) AS NameMI
In this example I've given you real data to use by defining a test value. It divides out the first name and last name from a concatenated record separated by a decimal point "." and stored as a single record. LEFT and RIGHT have had one subtracted from their values to omit the decimal point and SUBSTRING has one added to its value to omit the leading decimal point.
If you find this post useful please leave a comment and let me know how you used the information.
The query below is getting me Jones only, no initial. I have fiddled and fiddle and can't figure out how to get that H. Any help appreciated.
select
left(patient_name, (charindex(' ',patient_name) - 1))
I think what you are looking for is something like one of these:
LEFT(patient_name, CHARINDEX(' ', patient_name) - 1) + ' ' + SUBSTRING(patient_name, CHARINDEX(' ', REVERSE(patient_name)) + 2, 1)
OR
LEFT(patient_name, CHARINDEX(' ', patient_name) - 1) + ' ' + SUBSTRING(patient_name, CHARINDEX(' ', patient_name) + 1, 1)
The first one works if there are no other spaces in the record or if the last space is before their initial, the second works from the space after the last name.
set @nome = 'Oceano Pac￭fico da Silva';
select lower(substring(@nome, 1, charindex(' ', @nome)-1) + '.' +
reverse(substring(reverse(@nome), 1, charindex(' ', reverse(@nome))-1))) pri_ult
set @nome = 'Oceano.Pac￭fico.da.Silva';
select lower(substring(@nome, 1, charindex('.', @nome)-1) + '.' +
reverse(substring(reverse(@nome), 1, charindex('.', reverse(@nome))-1))) pri_ult
Sorry, now with '.' !
CustomerName is stored in my table in the following format:
MARK A & KAREN J MUMFORD
JACK & CARLY FERGUSON
JOSE GONZALEZ
How can I derive the Last name and First name from this?
First Name LastName
MARK A & KAREN J MUMFORD
JACK & CARLY FERGUSON
JOSE GONZALEZ
Thanks!