0

In our DB we have a name column and I am trying to select out first and last using but the data uses either | or space delimiters. Right now our code works for the space names but I need it to split on either one.

table.name
first last
first2|last2

select substr(upper(mid(vo.name, 1, locate(' ',vo.name)-1)),1,20) as 'First Name',
       substr(upper(mid(vo.name, locate(' ',vo.name)+1, 100)),1,20) as 'Last Name'
from table vo

Is there a way to look for either one?

jasonrfisher
  • 1
  • 2
  • First of all, trying to worry about first name vs. last name is a bit of a futile exercise. I have found over time that there are so many different variants of names that I just typically provide a single name field and let people enter data in it free form. That being said, if you insist on first/last name convention, why don't you first go through and sanitize your data to one format and then start enforcing data entry to only be done in that format? – Mike Brant Nov 25 '14 at 17:23
  • I don't have control over actually fixing the issue, ie sanitizing the data and enforcing the proper data entry. I am required to deliver this data to a vendor in the first last format. Believe me it wouldn't have been my first choice. – jasonrfisher Nov 25 '14 at 17:54

2 Answers2

0

I ended up going with a nested replace() function to santize the data inline but I feel like there might be a cleaner way.

    SELECT
      UPPER(SUBSTRING(vo.name, 1, LOCATE(' ', REPLACE(vo.name, '|', ' ')) - 1)) as 'First Name',
      UPPER(SUBSTRING(vo.name, LOCATE(' ', REPLACE(vo.name, '|', ' ')) + 1)) as 'Last Name'
from table vo
jasonrfisher
  • 1
  • 2
0
SELECT LEFT('FirstName|LastName', CHARINDEX (' ', 'FirstName|LastName') + CHARINDEX ('|', 'FirstName|LastName')-1)
SELECT RIGHT('FirstName|LastName', CHARINDEX (' ', 'FirstName|LastName') + CHARINDEX ('|', 'FirstName|LastName')-2)

this is assuming there is only one of '|' or ' ' - otherwise, this might work:

SELECT LEFT('FirstName |LastName', CHARINDEX (',', REPLACE(REPLACE(REPLACE('FirstName |LastName', ' ', ','), '|', ','), ',,', ','))-1)
Dale Sackrider
  • 1