fc948cfe   
   From: erik.lennart.jonsson@gmail.com   
      
   On 2011-02-11 16:10, Johnnyb wrote:   
   > On Feb 11, 7:03 am, "Bob Barrows"  wrote:   
   >> Johnnyb wrote:   
   >> > On Feb 10, 1:16 pm, "Bob Barrows"  wrote:   
   >> >> Johnnyb wrote:   
   >> >>> I receive this data in a column in a table:   
   >> >>> $8982162:2$905122:2$905PPA:   
   >> >>> 25$9601217:2$962B19:2$CSRI96:2$WDCOLACK:2$IDRBLACK2:2$IDRRED:2$   
   >>   
   >> >>> I need to make a view that will keep only the entries in that string   
   >> >>> that start with 'WDCO' and omit the rest   
   >>   
   >> >>> So I need to edit text in the process of copying the data from the   
   >> >>> table to the view. Hope this makes sense, sorry, I'm a newb.   
   >>   
   >> >>> SQL 2008 with patches.   
   >>   
   >> >> Please show us exactly what you want to extract from that string in   
   >> >> addition to attempting to describe/explain it. Based on your   
   >> >> description, you want the query/view to return:   
   >> >> WDCOLACK:2$IDRBLACK2:2$IDRRED:2$   
   >>   
   >> >> Is that your intent?   
   >> >> Or do the $ symbols represent delimiters for "entries" in the   
   >> >> string, in which case this is what you want to return:   
   >> >> WDCOLACK:2   
   >>   
   >> > Sorry, it is the second answer. I get random entries - like   
   >> > $8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$CSRI96:2$WDCOLACK:   
   >> > 2$IDRBLACK2:2$IDRRED:2$. The $'s are delimiters, and I only want to   
   >> > capture the entires that start with 'WDCO'.   
   >>   
   >> > Thanks   
   >>   
   >> Can there be multiple occurrences in the same string? If so, how do you want   
   >> those handled? Again, show us, don't just describe it.   
   >   
   > There will never be multiple occurrences in the same string.   
   >   
   > This is how it works:   
   >   
   > There are ~1100 possible entires ('$' delimited) that will show up in   
   > the original string. There are no duplicates.   
   >   
   >  $8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$CSRI96:2$WDCOLACK:   
   > 2$IDRBLACK2:2$IDRRED:2$   
   >   
   > This is an example of what might come into the original table. I need   
   > to filter this list of entries so that the view have only the ones   
   > that start with WDCO. So no matter how many '$' delimited entries show   
   > up in the original table, I only want to see the WDCO ones in the view.   
      
   Not sure I fully understould the problem, but ...   
      
   with t (s) as (   
      select   
   '$8982162:2$905122:2$905PPA:25$9601217:2$962B19:2$CSRI96:2$WDCOL   
   CK:2$IDRBLACK2:2$IDRRED:2$'   
   ), t2 (s, start_pos) as (   
      select s, CHARINDEX('$WDCO', s) as start_pos from t   
   )   
   select   
     SUBSTRING(s,start_pos, CHARINDEX('$', s, start_pos+1) - start_pos)   
   from t2;   
      
   $WDCOLACK:2   
      
   /Lennart   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)