Solved

mysql parse string value

Posted on 2009-04-08
4
1,951 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
ID: 24103231
Why do you create your own "fake" columns inside a table ?
Why not use the columns of a table ?
0
 

Author Comment

by:bray007
ID: 24103270
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
ID: 24103339
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
ID: 31568326
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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