CODEWITHSUNDEEP
mysqldatabase
Wheelz
Wheelz

Reputation: 219

Get part of a string in a sql statement based on a character

I have a sql statement as below

SELECT cc.id, SUBSTRING(cc.path,2) AS pointer
FROM table AS CC

the field path is like

/2/3 or /3/23/4

What I'm trying to get is the number AFTER the second slash. I can't use SUBSTRING(cc.path,2,1) because it isn't always a single digit number. How can I get that number based on getting a number at the end OR between 2 slashes?

Thanks

Kieran

Upvotes: 3

Views: 69

Answers (3)

Giles
Giles

Reputation: 1687

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(cc.path,'/',3),'/',-1)

see: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

Upvotes: 2

Ritesh
Ritesh

Reputation: 4756

For your second condition please try :

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

Adjust arguments as per requirements.

Upvotes: 0

Ali Malik
Ali Malik

Reputation: 1341

You can provide the length (3rd parameter) by computing the starting position of next '/' in your string. you can get something like this..

substring(originalstring,(string,stringpos(string,'/'))

Additionaly check this articlesql substring(). Hope this helps

Upvotes: 1

Related Questions