0

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?

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

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

   SUBSTRING_INDEX( SUBSTRING_INDEX(path, '/', 2), '/', -1) 
| improve this answer | |
  • 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 – Wheelz Nov 14 '16 at 12:18
  • Then you are doing something wrong. Show us the exact query you run. – ypercubeᵀᴹ Nov 14 '16 at 12:30
  • SELECT substring_index( substring_index(cc.path),'/',2),'/',-1) as path FROM mdl_course_categories as cc; – Wheelz Nov 15 '16 at 10:00
  • 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. – ypercubeᵀᴹ Nov 15 '16 at 10:05
  • Thats sorted it. Cheers – Wheelz Nov 15 '16 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.