Solved

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

Posted on 2010-09-02
6
387 Views
Last Modified: 2012-05-10
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?

Thanks
0
Comment
Question by:jdr0606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
MPKR earned 500 total points
ID: 33591822
You can use the PATINDEX function. It returns the first appearing position of a string inside another.
Like this>
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
 
0
 
LVL 3

Expert Comment

by:avalenzuela
ID: 33591849
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
0
 
LVL 5

Expert Comment

by:almander
ID: 33591856
select Substring('DHCP-5D-PROMO', 0, len('DHCP-5D-PROMO') - charindex('-',Reverse('DHCP-5D-PROMO'))+1)
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 5

Expert Comment

by:almander
ID: 33591862
Nice answer avalenzuela
0
 
LVL 3

Expert Comment

by:avalenzuela
ID: 33591866
you can try this also to see how it works..


select
  Replace('MYPRODUCTX-PROMO', '-PROMO',''),
  Replace('Myproduct-XXX-PROMO', '-PROMO','') ,
  Replace('Myprod-yyy-PROMO', '-PROMO','')
0
 
LVL 4

Expert Comment

by:MPKR
ID: 33591878
avalenzuela, this should be a good solution - but  not the one he has asked for  :-]
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to install and use the NTBackup utility that comes with Windows Server.
A list of top three free exchange EDB viewers that helps the user to extract a mailbox from an unmounted .edb file and get a clear preview of all emails & other items with just a single click on mailboxes.
In this video we show how to create an Accepted Domain in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Ac…
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question