mysql parse string value

I have a value placed into a varchar field in Mysql.  I need to perform a query to parce a certain element of the string.  For example:
FILE_SAMP_100_23456_001_01012009_1245-1-4.txt
I need to extract the value 23456 to as a "column" in a select query.  The delimiter should be the "_", not count the number of characters to 23456 because the FILE_.... could be FILESTODAY_...., thus throwing off a character count.

I provided the sql select I am using, but it produces:
FILE_SAMP_100_23456
as the output, where I only want 23456.
I would like to know if I can do this in a single select statement.

500 points. I think it is easy, I am just missing something.

Thanks.

select fname, substring_index(fname, '_',4) as mybatchno, substring_index(fname, '_', -3) as xmybatchno
from dbtable

Open in new window

bray007Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GarthSnyderConnect With a Mentor Commented:
MySQL's string processing functions are described here.

In this case, just use a negative count to remove the prefix and then run substring_index again to remove the suffix. In other words:

substring_index(substring_index(fname, '_', -4), '_', 1)

select fname, substring_index(substring_index(fname, '_', -4), '_', 1) as mybatchno, substring_index(fname, '_', -3) as xmybatchno
from dbtable

Open in new window

0
 
DubouxCommented:
Why do you create your own "fake" columns inside a table ?
Why not use the columns of a table ?
0
 
bray007Author Commented:
that's what I am trying to get to. I receive a file from a customer, and need to export the pseudo column to list the batchno.  I was trying to do this with mysql, rather than having to re-write an already standardized import script in shell to export the batchno. (I can certainly do it with shell and cut -d, but am trying to keep customization within the confines of the DB where ever possible.)  I do have a column for the batchno to land in, eventually.  but for the sake of the question, I was trying to keep it simple.  If I can get it to work with select, then I can set the rest up.  Unless you have any other opinions, like within a function, I am all ears, or eyes on the function code.  Thx.
0
 
bray007Author Commented:
Perfect.  I knew it was simple.  I actually changed it to substring_index(substring_index(fname, '_', 4), '_', -1), just cause I think they may eventually start adding new subnodes to the files.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.