SQL query to remove left and right characters in a field

Question

I have a sql field that i need to edit out characters on the left and right.
The table name is: cdr
The field name is: dstchannel
The dstchannel field shows the following data: Local/872@from-internal-6acb,1
I just the need the 872 as output
The field always has the same amount of data, so the 872 will be constaint on its position.
How would I go about writing a sql query for mysql?

If I have another query that I want to include this into, how would I do that?

Thank you

Answer 1

Assuming the number is always 3 characters in length:

SELECT
  SUBSTRING(dstchannel, 7, 3) AS x
FROM cdr

If the length of the number is variable, you'll need to look for the '@'. In T-SQL (SQL Server), you can use CHARINDEX:

SELECT
  SUBSTRING(dstchannel, 7, CHARINDEX('@', dstchannel, 7) - 7) AS x
FROM cdr

Answer 2

If the delimiters are always '/' and '@':

select substring(dstchannel, 
  locate('/', dstchannel) + 1, 
  locate('@', dstchannel) - locate('/', dstchannel) - 1);