Solved

Select using Substring + a Join

Posted on 2008-10-10
1
189 Views
Last Modified: 2012-05-05
I have the following:

select Top(5) substring(ParentId, 0, charindex('-',ParentId,1)) as Parent from assets

1. :
2. : 72
3. : 51
4. : 168
5. : 111

and I have:

select Top(5) parentid from assets

which gives:

1. : 56
2. : 72-10672
3. : 51-10610
4. : 168-10658
5. : 111-10624


How would I go about doing a join where I include ALL parentID's which do not have a - ? and where it uses the first select statement to extract the first part of the ParentID?
0
Comment
Question by:directxBOB
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22685656
what about this:

select Top(5) case when parentid like '%-%' then substring(ParentId, 0, charindex('-',ParentId,1)) else parentid end as Parent from assets 

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now