CODEWITHSUNDEEP
mysql
Johannes B
Johannes B

Reputation: 33

MySQL - Replacing first space with characters

I have a column 'full_name' in a table of users. Seeing as I don't want to change the table structure, I am looking for a way to replace the first space in each name with two characters (%$ for instance), so that my code will know where to split the first and second name.

This will make future name entries work even if the given name is a double name.

Thanks!

Upvotes: 2

Views: 430

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

As others have suggested, the best thing might be to redesign your schema to have separate columns for the first and last names. If you cannot do this and you need a query to extract out the first and last names, then consider the following queries. I don't think you need to add a special delimeter. Rather, you can just use standard MySQL string functions.

Here is a query you can use to extract the first name (everything up until the first space):

SELECT SUBSTRING(full_name, 1, INSTR(full_name, ' ')-1)

Here is a query for the remainder:

SELECT SUBSTRING(full_name,
                 INSTR(full_name, ' ') + 1,
                 CHAR_LENGTH(full_name) - INSTR(full_name, ' '))

Upvotes: 1

Sonny
Sonny

Reputation: 8326

I would write a conversion script in PHP or whatever language you're using in your stack. You would execute it right before deploying the new code that uses your specified format.

Upvotes: 0

Related Questions