[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Select using Substring + a Join

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
directxBOB
Asked:
directxBOB
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now