RE: substring query

2010-06-10 Thread Jay Blanchard 
[snip]
I am looking for some guidance on creating a substring query.  I have
a column that stores a path to a file.  I would like to extract that
file extension and that is it and display it on my results.  However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'.  The only common they all have is the period
before the extension.  Anyone created a nested substring query that
can do what I am looking to do?
[/snip]

From the manual -
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_su
bstr

SELECT SUBSTRING('myString', -3)

The result would be 'ing' in this case. Sub your string for myString

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: substring query

2010-06-10 Thread Mark Goodge 

On 10/06/2010 16:55, Aaron Savage wrote:

I am looking for some guidance on creating a substring query.  I have
a column that stores a path to a file.  I would like to extract that
file extension and that is it and display it on my results.  However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'.  The only common they all have is the period
before the extension.  Anyone created a nested substring query that
can do what I am looking to do?


SUBSTRING_INDEX should do what you want.

SELECT SUBSTRING_INDEX('myfile.path','.',-1)
= 'path'

SELECT SUBSTRING_INDEX('myfile.pth','.',-1)
= 'pth'

or, in a version that's closer to real life usage:

SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

Mark
--
http://mark.goodge.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: substring query

2010-06-10 Thread Aaron Savage 
Thanks Guys for you insights.

It may be a little more complicated then I made it out to be.

I have tried this select substring_index(myfiled,'.',-2) from mytable.
 This has gotten me to a good starting point.

But I still have two problems.  After the extension there is a space
and more wording.  I want to cut that off.  Also, some paths do not
have an extension and I am trying to ignore those.  So simply.  I am
just trying to pull out the file extension but there were some
conditions I did not list.

-Aaron


 SUBSTRING_INDEX should do what you want.

 SELECT SUBSTRING_INDEX('myfile.path','.',-1)
 = 'path'

 SELECT SUBSTRING_INDEX('myfile.pth','.',-1)
 = 'pth'

 or, in a version that's closer to real life usage:

 SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable

 http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

 Mark
 --
 http://mark.goodge.co.uk



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: substring query

2010-06-10 Thread Jay Blanchard 
[snip]
It may be a little more complicated then I made it out to be.

I am just trying to pull out the file extension but there were some
conditions I did not list.
[/snip]

Thank you for that update, would have been good to have from the start.

SELECT SUBSTRING_INDEX('my.doc','.',-1)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: substring query

2010-06-10 Thread Aaron Savage 
Sorry Jay,

Here is what I came up with.

select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from
mydatabase group by  MyColumn;

That appears to yield what I need.  I just need to filter out the
results that do not have an extension.

-Aaron


On Thu, Jun 10, 2010 at 9:28 AM, Jay Blanchard jblanch...@pocket.com wrote:

 Thank you for that update, would have been good to have from the start.

 SELECT SUBSTRING_INDEX('my.doc','.',-1)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: substring query

2010-06-10 Thread Jay Blanchard 
[snip]
Here is what I came up with.

select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from
mydatabase group by  MyColumn;

That appears to yield what I need.  I just need to filter out the
results that do not have an extension.
[/snip]

You can exclude results that do not have a period in them if this is the
only period

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org