Solved

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

Posted on 2010-09-02
6
385 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2010 mailbox move 7 48
powershell mailbox move question 8 44
SQL- GROUP BY 4 22
Exchange 2013 to another 2013 forest move request 2 14
Utilizing an array to gracefully append to a list of EmailAddresses
Find out what you should include to make the best professional email signature for your organization.
In this video we show how to create a mailbox database 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 >> Data…
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…

735 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