Solved

mysql parse string value

Posted on 2009-04-08
4
1,942 Views
Last Modified: 2012-05-06
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
Comment
Question by:bray007
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Duboux
Comment Utility
Why do you create your own "fake" columns inside a table ?
Why not use the columns of a table ?
0
 

Author Comment

by:bray007
Comment Utility
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
 
LVL 3

Accepted Solution

by:
GarthSnyder earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:bray007
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now