mysql locate multiple delimiters

Question

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?

Answer 1

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

Answer 2

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)