To: Jim Finerfrock 
> Ari,
> 
> How are you?
> 
> I have a problem using LTRIM and RTRIM.
> 
> The column contains:
> 
> GCI-DCWZI-07154
> GCI-DCWZI-07155
> GCI-DCWZI-07156
> GCI-DCWZI-07157
> GCI-DCWZI-07159
> GCI-DCWZI-07160
> GCI-KSSTATE-03095
> GCI-KSSTATE-03096
> GCI-KSSTATE-03097
> GCI-KSSTATE-03098
> GCI-KSSTATE-03099
> GCI-KSSTATE-03100
> GCI-KSSTATE-03101
> DS-ROGOK-00323
> DS-ROGOK-00340
> DS-ROGOK-00377
> DS-ROGOK-00414
> 
> I am trying to return back a unique list of everything between the two '-'
> (hyphens).
> 
> My statement tried:
> 
> SELECT DISTINCT LTRIM(RTRIM(catalogid,'-'),'-')
> FROM catalog WHERE countryid = 'ZI'
> 
> SELECT UNIQUE(LTRIM(RTRIM(catalogid,'-'),'-')
> FROM catalog WHERE countryid = 'ZI'
> 
> For some reason the '-' is not being recognized. When I tried 'GCI-' it
> worked, but I would like to not have to put anything before or after the '-'
> because of the number of different values to the left and right of the '-'.
> 
> FYI, the GCI-KSSTATE & DS-ROGOK records are only to show you that I cannot use
> the SUBSTR functionality because it all varies between all the hyphens.
> 
> Thanks in Advance,
> 
> Jim Finerfrock
> GeoComm International Corporation
> 4565 Commercial Dr., Suite D
> Niceville, Fl. 32578
> Voice: 850.897.0110
> Fax:   850.897.1001
> 
Jim,

Thanks for the email. I am doing well....just got back from East Europe
which is my excuse for not getting back to you sooner. I've been gone a
month....

Anyway, I hope all is well at GeoComm. I have an answer to your question
that you sent a while back.

The following SQL will work:

SELECT substr(catalogid,instr(catalogid,'-')+1,
       instr(catalogid,'-',-1)-instr(catalogid,'-')-1)
FROM catalog
WHERE countryid = 'ZI';

What you need to know is that "substr('12-456-89','-')" gives the position
of the FIRST '-', in this case 3. "substr('12-456-89','-',-1) gives the
position of the FIRST '-' FROM THE END OF THE STRING, in this case 7.

Best regards Jim,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 310+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page dth="16" height="18"> Back to Ari Kaplan's Home Page