SQL has a few, limited, string manipulation functions. One of this is
'strpos'
with return the position of a sub-string within the string, and another
is
'substr' which return a substring based on positions. You'd think that
these
would make it easy, but there doesn't seem to be a way to get the _last_
occurance of a string. So, unless you know more about the format of this
string than is given in the example (i.e., is it always the same number
of catagories? Is the number
always the same length?) it's not possible, within SQL.
I assume you're just doing this one-off, for data importing or
something? The following assumes three levels of categories, like in the
example:
test=> select * from t;
long |short
--------------------------------------------------------+-----
categoryname/subcategoryname/someotherinformation/012345|
(1 row)
test=> select long from t;
long
--------------------------------------------------------
categoryname/subcategoryname/someotherinformation/012345
(1 row)
test=> select substr(long,strpos(long,'/')+1) from t;
substr
-------------------------------------------
subcategoryname/someotherinformation/012345
(1 row)
test=> select
substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1)
from t;
substr
---------------------------
someotherinformation/012345
(1 row)
test=> select
substr(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),strpos(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),'/')+1)
from t;
substr
------
012345
(1 row)
update t set
short=substr(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),strpos(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),'/')+1);
UPDATE 1
test=> select * from t;
long | short
--------------------------------------------------------+------
categoryname/subcategoryname/someotherinformation/012345|012345
(1 row)
test=> select short from t;
short
------
012345
(1 row)
Kevin Heflin wrote:
>
> Just hoping some magic SQL can get me out of this one easily enough.
>
> I have a field of type varchar a sample would look something like this:
>
> 'categoryname/subcategoryname/someotherinformation/012345'
>
> all I want in this field is that last bit of information after the last
> '/' ie: '012345'
>
> I'd like to either replace this field with this number alone or insert
> into another newly created field.
>
> Any suggestions would be appreciated.
>
-
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
