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
Solved

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

Posted on 2010-09-02
6
384 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
To show how to generate a certificate request 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 Servers >> Certificates…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

809 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