Grouping by part of a field. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping by part of a field.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-12-03 : 17:04:12

I have a part number field with part numbers like:

12-190-6/1P15
12-190-6/1P54
12-190-6/1P70
12-190-6/1P15 MU
12-132P4
12-132P15
12-132P6
10-152P85
10-152P58

In essence I need to strip off everything after the P so that I can group the parts.

All of these should be grouped together as 12-190-6/1 for example.
12-190-6/1P15
12-190-6/1P54
12-190-6/1P70

How can I query a table and group by that part of the part number field?

Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 17:08:24

You can select using substring and charindex.
But in group by you have to do that a second time.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 17:11:44

Like this:
select
substring('12-190-6/1P15',1,charindex('P','12-190-6/1P15')-1)

No, you're never too old to Yak'n'Roll if you're too young to die.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-12-03 : 17:15:17

When I tried this:

select substring(INM.fpartno,1,charindex('P',INM.fpartno)-1) from dbo.inmast INM

I got the following:
SQL Server Database Error: Invalid length parameter passed to the substring function.

I assume because not all part numbers have 'P' in them. Right?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-12-03 : 17:21:53

I think this will work:

SELECT substring(INM.fpartno, 1,
                 (CASE charindex('P', INM.fpartno)
                     WHEN 0 THEN len(INM.fpartno)
                     ELSE (charindex('P', INM.fpartno)) - 1
                  END))
  FROM dbo.inmast INM

What do you guys think?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 17:41:17

Yes this is ok and sorry - I was a little too lazy to post the eventuality of missing 'P's.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources