Display a field using regex

Question

I have a table with a field in with data such as:

id | path
1 | /2/9/1 
2 | /2/4/11 
3 | /1/3/11 
4 | /5/3/2 
5 | /2/1/4

I'm wanting to do a select query to show only the first number between the slashes. Can you use a Regex in the select part of a query? If so, what is the syntax to find what I need?

• Philᵀᴹ: Is the number always 0-9? If so, there's easy ways of doing this
• Wheelz: Its always a number, but more than likely it will be something like /143/23/153

Answer 1

You can use SUBSTRING_INDEX() function for that, no need for regexes:

 SUBSTRING_INDEX( SUBSTRING_INDEX(path, '/', 2), '/', -1) 

• Wheelz: I get the error message 1582: Incorrect parameter count in the call to native function 'SUBSTRING_INDEX' Its fine when I put an actual string in there but trying to do it on a field errors
• ypercubeᵀᴹ: Then you are doing something wrong. Show us the exact query you run.
• Wheelz: SELECT substring_index( substring_index(cc.path),'/',2),'/',-1) as path FROM mdl_course_categories as cc
• ypercubeᵀᴹ: The "incorrect parameter count" indicates that you missed a comma or you misplaced parentheses and that's what you did. There is an extra parenthesis after cc.path. Remove it.
• Wheelz: Thats sorted it. Cheers