Solved

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

Posted on 2010-09-02
6
386 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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