getting the specific string in sql

Question

I am using MySQL 4.1.21 (win 32) on Windows 7.

I have a field called "path" in table "test" some rows look like following:

/date/sunday/morning/
/date/sunday/morning/9
/date/wednesday/morning/11
/date/monday/morning/10
/date/monday/afternoon/
/date/friday/wholeday/10/pm

...etc.

I want to get all days after /date/ till the next '/' starts for example: sunday or wednesday or morning as the results.

thank you-

eggyal · Accepted Answer · 2012-08-30 17:37:54Z

1

You can use MySQL's SUBSTRING_INDEX() function, together with a filter based on a pattern match:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', 3), '/', -1)
FROM   test
WHERE  path LIKE '/date/%'

See it on sqlfiddle.

answered Aug 30, 2012 at 17:37

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1