Menu
I am working with a database of products, trying to extract the product color from a combined ID/color code column where the color code is always the string following the last hyphen in the column. The issue is that the number of hyphens, product ID, and color code can all be different.
Here are four examples:
ABC123-001
BCD45678-0165
S-XYZ999-M2235
A-S-ABC123-001
The color codes in this case would be 001, 0165, M2235, and 001. What would be the best way to select these into their own column?
561
To find the index of the specific character, you can use the CHARINDEX(character, column) function where character is the specific character at which you'd like to start the substring (here, @ ). The argument column is the column from which you'd like to retrieve the substring; it can also be a literal string.
If you want to get the index of the last space in a string of words, you can use this expression RIGHT(name, (CHARINDEX(' ',REVERSE(name),0)) to return the last word in the string. This is helpful if you want to parse out the last name of a full name that includes initials for the first and /or middle name.
Learn MySQL from scratch for Data Science and Analytics To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.
I think the following does what you want:
select right(col, charindex('-', reverse(col)) - 1)
In the event that you might have no hyphens in the value, then use a case:
select (case when col like '%-%'
then right(col, charindex('-', reverse(col)) - 1)
else col
end)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
