Thread

1. select from into question

   Kevin Heflin <kheflin@shreve.net> — 1999-05-18T02:36:05Z

   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.
   
   Kevin
   
   
   
   
   
   --------------------------------------------------------------------
   Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
   VP/Mac Tech           | 333 Texas St #175    | FAX:318.221.6612
   kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
   --------------------------------------------------------------------
   
   
   

2. Re: [GENERAL] select from into question

   Ross Reedstrom <reedstrm@rice.edu> — 1999-05-18T17:20:55Z

   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