Mysql Remove First and Last Part of a String - Explode Like Function?

Question

Alright, so I have a column that has strings set up like this...

string/1984-Michael-Jackson-133298
string/1984-Steve-Van-Bure-392289

I'm trying to get the name out of this string. The problem, as shown, is there are names with 3 sections like "Steve Van Bure". I know how to use SUBSTRING_INDEX to get the middle two words, but I'm not sure how to go about making sure to include ones with 3. So I figure the best option would be to take out the first and last options and whatever is in the middle is the name.

Any clue on how to do this with Mysql Functions? Thanks

This might help: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Paul Spiegel, Jan 16 '17 at 21:58
Remove everything up to the 1st dash and everything after the last dash.. What's left regardless of how many dashes? (this assumes the 1st set and last set can't have multiple dashes) — xQbert, Jan 16 '17 at 22:01
That's the question. How do you remove everything up to the first dash and everything after the last dash? — Martavis Griffin, Jan 16 '17 at 22:10

james_bond · Answer 1 · 2017-01-16T23:17:39.923

Use a combination of SUBSTRING_INDEX, REPLACE and TRIM to remove all the chars up to the first - (non inclusive) and all the chars from the last - (non inclusive) to the end of the string. From there just remove both the remaining - at the beginning and end of the remaining string.

SELECT TRIM(BOTH '-' FROM REPLACE(REPLACE([YOUR FIELD], SUBSTRING_INDEX([YOUR FIELD], '-', 1), ''), SUBSTRING_INDEX([YOUR FIELD], '-', -1), '')) FROM [your table]

VKK · Accepted Answer · 2017-01-16T22:45:26.770

You can use the following code where str is the name of your column and tablename is the name of your table. If you're only looking to tease out the name portion of the string, then just use the portion of the SELECT clause labeled (conveniently) name:

SELECT SUBSTRING(str,LOCATE("/",str)+1,LOCATE("-",str)-LOCATE("/",str)-1) AS year, 
SUBSTRING(str,LOCATE("-",str)+1, LENGTH(str) - LOCATE("-",REVERSE(str)) - LOCATE("-",str)) AS name,
SUBSTRING(str,LENGTH(str) - LOCATE("-",REVERSE(str)) + 2) as number
FROM tablename;

Explanation

MySQL has a very limited set of built in string manipulation functions compared to your standard general purpose programming language, but that is likely because in most cases good table structure negates the need for such functions.

Anyway, there's a variety of ways to achieve what you're looking for. One way is to use the SUBSTRING function in combination with the LOCATE function, REVERSE, and LENGTH function. We use REVERSE and LENGTH to get the position of the last "-".

Getting the first part of the string (the year) is relatively straightforward - we use the SUBSTRING function which takes as it's parameters the column name, inclusive starting location, and length. The starting location is the location of the first "/", plus 1 since we don't actually want the "/" (LOCATE("-",str)+1). The length is the distance from the first "/" to the first "-" or in SQL LOCATE("-",str)-LOCATE("/",str)-1.

Now let's come back to the second part of the string (the name) later. For the third part of the string (the number) the magic lies in the REVERSE function used in conjunction with LENGTH. We ultimately want to find the position of the last "-" and then get the substring from the last "-" to the end of the string. Unfortunately, MySQL doesn't have a function to return the last occurrence of a character. The trick to get the position of the last "-" is to get the location of "-" in the reversed string and then subtract that from the length of the string. In SQL, that's LENGTH(str) - LOCATE("-",REVERSE(str)) + 2.

Now we have the information we need to get that middle portion (the name). We use the SUBSTRING function again passing it the column name, location of the first "-" and length until the last "-". And that's it!

Mysql Remove First and Last Part of a String - Explode Like Function?

2 Answers2