Solved

mysql parse string value

Posted on 2009-04-08
4
1,969 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

636 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