CODEWITHSUNDEEP
mysql
PQuix
PQuix

Reputation: 83

How can I return only a specific part of a column?

So I have a column that returns a string that looks like this:

part1 > part2 > part3

I want to be able to return only part 2 when I select that row, but I can't quite figure out how to do it. The closest I've gotten is through this statement:

SELECT
SUBSTRING(column_name, INSTR(column_name, ">") +2)

FROM
table;

Which returns part2 > part3.

I looked into using RegEx, but I'm really not steady on that and I can't seem to wrap my head around how I would build that statement... If anyone could give me some pointers, that would be greatly appreciated :)

Upvotes: 0

Views: 120

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Use Trim() function alongwith Substring_Index():

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, " > ", 2), " > ", -1)) 
FROM table

Details:

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

  • SUBSTRING_INDEX(column_name, " > ", 2) returns "part1 > part2"
  • Further SUBSTRING_INDEX(string, " > ", -1) returns "part2"
  • Note the usage of leading and trailing spaces in the delimiter string " > "
  • TRIM clears all leading and trailing spaces (if any left).

Upvotes: 3

Bobert1234
Bobert1234

Reputation: 89

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ">" ,2), ">", -1)

mysql has a very useful SUBSTRING_INDEX function which gets the string only up to a particular substring or character. it also allows you to go backwards from the end of the string using a negative number. also, note that if you want to TRIM the spaces around the ">" (as someone else suggests) you can use " > " instead of just ">"

Upvotes: 0

Related Questions