Link to home
Start Free TrialLog in
Avatar of jdr0606
jdr0606Flag for United States of America

asked on

Using a select in SQL 2005 to search on partial field content

I have a table with part numbers that have -PROMO on the right end if they are part of a monthly promotion, however I want to select the table and exclude the portion of the prodct number that says -PROMO.

In other words I might have a product # DHCP-5D with a QTY of 5 and a Promotional product DHCP-5D-PROMO (which actually is the same as the DHCP-5D just with a -PROMO on the end)

How do I "FIND" where the '-PROMO' starts so that I can substring it out?

Avatar of MPKR
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avalenzuela
Just replace the '-PROMO' with a zero length string..

Check this..
Print  Replace('MYPRODUCT-PROMO', '-PROMO','')

the REPLACE function can be used on any select statment
Avatar of almander

select Substring('DHCP-5D-PROMO', 0, len('DHCP-5D-PROMO') - charindex('-',Reverse('DHCP-5D-PROMO'))+1)
Nice answer avalenzuela
you can try this also to see how it works..

  Replace('MYPRODUCTX-PROMO', '-PROMO',''),
  Replace('Myproduct-XXX-PROMO', '-PROMO','') ,
  Replace('Myprod-yyy-PROMO', '-PROMO','')
avalenzuela, this should be a good solution - but  not the one he has asked for  :-]