substring query

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?

Thanks,
Aaron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: substring query

[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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: substring query

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=gcdmg-mysql-2@m.gmane.org

Re: substring query

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=gcdmg-mysql-2@m.gmane.org

RE: substring query

[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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: substring query

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 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=gcdmg-mysql-2@m.gmane.org

RE: substring query

[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=3Dgcdmg-mysql-2@m.gmane.o rg