Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1985
  • Last Modified:

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

0
bray007
Asked:
bray007
  • 2
1 Solution
 
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
 
GarthSnyderCommented:
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now