Search code examples
sqlpervasivepervasive-sql

Extract Text, Removing Characters After "-"

I have a column Plans like this:

   Plans
   OXF-PL10-A
   OXF-PL10-C
   OXF-PL10-P
   OXF-PL10VE
   OXF-PL2-P 
   OXF-PL3-P

How can I just grab the text without the characters after the 2nd "-"

I want my data as follows:

   Plans
   OXF-PL10
   OXF-PL10
   OXF-PL10
   OXF-PL10VE
   OXF-PL2
   OXF-PL3

Note: I'm using regular standard ANSI SQL not MySQL

Solution

  • Reposted my comment as requested:

    select 
  case when locate('-', plans, 6) > 1 then left(plans, locate('-',plans, 6)-1) 
       else plans 
  end 
from table