Re: Substrings in Informix SQL

In article <35e466fa.3621574@allnews.infi.net>, Hal Larson <hlarson@mail.hesch-prod.com> wrote: > > >SELECT DISTINCT Product_Code[1,1], Product_Name > FROM Product_List > WHERE Product_Code[2,2]='-' >UNION >SELECT DISTINCT Product_Code[1,2], Product_Name > FROM Product_List > WHERE Product_Code[3,3]='-' >UNION >SELECT DISTINCT Product_Code[1,3], Product_Name > FROM Product_List > WHERE Product_Code[4,4]='-' >... all the way up to the case where it's a 48-char product code > followed by a dash and a 1-char mfr... >SELECT DISTINCT Product_Code, Product_Name > FROM Product_List > WHERE Product_Code NOT LIKE '%-%'; It would be easy enough using 4GL - or even running the output of the query through unix commands (eg cut -f1 -d"-" | sort -u) - but in pure SQL it's registering a big "Hmmmmm" in my brain right now. But allow me to make the observation that you can throw out the DISTINCTs - UNION will eliminate duplicates anyway. - Paul