Extracting String

Hi,

How do I extract a word and sum up my results as per that word from a field? For example,
I've a table with columns Branch, Name, Count

Branch                              Name                Count
Universe_Branch1_Atl         Atlanta             23
Universe1_Branch1_Cn        Cincinatti         10
Universe2_Branch2_Ch        Chicago           15

I want to output results by grouping by Branch1 and Branch2 (rather than displaying the whole word, I just want to display Branch1 or Branch2)as follows:

Branch1    Atlanta      23
Branch1    Cincinatti   10
Branch2    Chicago     15


Thanks
SKM061104Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Try it this way:

Select PARSENAME(REPLACE(Branch, '_', '.'), 2)),
          Name,
          Sum(Count) Count
From Table1
Group By PARSENAME(REPLACE(Branch, '_', '.'), 2)), Name
0
 
arbertCommented:
Something like this should be on the right track.  Basically, you can use Charindex to see if the string exists in your column and then group by that:

select count(*),
case when charindex('1',branch)>0 THEN 'Branch1'
        when charindex('2',branch)>0 then 'Branch2'
end as BranchName
from yourtable
group by
case when charindex('1',branch)>0 THEN 'Branch1'
        when charindex('2',branch)>0 then 'Branch2'
end
0
 
paeloCommented:
If the format is always like that, you can use:

SELECT SUBSTRING(y.yourfld, CHARINDEX('_',y.yourfld,0)+1, CHARINDEX('_',y.yourfld,CHARINDEX('_',y.yourfld,0)+1) - CHARINDEX('_',y.yourfld,0)-1) As [Branch]
FROM dbo.yourtable y
GROUP BY SUBSTRING(y.yourfld, CHARINDEX('_',y.yourfld,0)+1, CHARINDEX('_',y.yourfld,CHARINDEX('_',y.yourfld,0)+1) - CHARINDEX('_',y.yourfld,0)-1)

There may be a less CHARINDEX intensive solution.

-Paul.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Anthony PerkinsCommented:
Select SUBSTRING(Branch, CHARINDEX('_', Branch) + 1, CHARINDEX('_', Branch, CHARINDEX('_', Branch) + 1) - 1),
          Name,
          Count
From Table1
0
 
Anthony PerkinsCommented:
Here is a simpler solution (that does not use CHARINDEX):

Select PARSENAME(REPLACE(Branch, '_', '.'), 2)),
          Name,
          Count
From Table1
0
 
paeloCommented:
Very nice.

-Paul.
0
 
SKM061104Author Commented:
Hi all,

Sorry, a slight twist to my previous question:
I've  a table, say 'Table1' like below:
Branch                              Name                Count
Universe_Branch1_Atl         Atlanta             23
Universe1_Branch1_Cn        Cincinatti         10
Universe1_Branch1_Cn2      Cincinatti          24
Universe2_Branch2_Ch        Chicago           15


And I want the above to output as follows:

Branch1   Atlanta      23
Branch1   Cincinatti   34
Branch2   Chicago     15

So, what is the easiest way? What would I be grouping by?

Thanks!!
0
 
Anthony PerkinsCommented:
>>Any objections<<
None :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.