how to group by perfix - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to group by perfix

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:12:22

How do i group bye the prefix.
I have data like this:
ID Col1
1 Abc001
2 Xyz001
3 Abc002
4 Mno-001
5 xyz002
6 abc003
7 mno-002

The Output looks like this:
1 Abc
2 xyz
3 Mno-

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:16:54

will prefix be always three characters long?


SELECT LEFT(Col1,3) AS Prefix,COUNT(ID) AS RecCnt
FROM Table
GROUP BY LEFT(Col1,3)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 12:16:57

I think what you need is this

declare @temp table ( id int ,col1 varchar(64))
insert into @temp
select 1, 'Abc001' union all
select 2, 'Xyz001' union all
select 3, 'Abc002' union all
select 4, 'Mno-001' union all
select 5, 'xyz002' union all
select 6,'abc003' union all
select 7, 'mno-002'

select * from @temp
order by left(col1,3)

Isn't it??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:18:21

quote:
Originally posted by raky

I think what you need is this

declare @temp table ( id int ,col1 varchar(64))
insert into @temp
select 1, 'Abc001' union all
select 2, 'Xyz001' union all
select 3, 'Abc002' union all
select 4, 'Mno-001' union all
select 5, 'xyz002' union all
select 6,'abc003' union all
select 7, 'mno-002'

select * from @temp
~~order~~group by left(col1,3)

Isn't it??

question was to group by not order by

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-18 : 12:19:25

Why does he need group by? He isn't using an aggregate function..

I think order by Col1 works for his purpose.

lol I think the question was answered correctly but asked incorrectly.
(No need for group by but answer correctly reflects group by).

r&r

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 12:19:39

Actually he doesn't asked count so i thought that he may want order by but asked us as group by...

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:21:59

Thanks for reply. but its not fix how many char on prefix. it could be more than 3 chars too.

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-02-18 : 12:26:24

Is the string after the prefix always 3 chars?

GROUP BY LEFT(Col1, LEN(Col1) - 3)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:27:14

quote:
Originally posted by rudba

Thanks for reply. but its not fix how many char on prefix. it could be more than 3 chars too.

then try this


SELECT LEFT(Col1,CHARINDEX('[0-9]', Col1)-1) AS Prefix,COUNT(ID) AS RecCnt
FROM Table
GROUP BY LEFT(Col1,CHARINDEX('[0-9]', Col1)-1)

and if you want order by use this in order by instead

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:30:01

thanks revdnrdy,raky, bjoerns
its not always 3 chars prefix. and next script i have to do something so i need group by prefix only

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 12:36:57

quote:
Originally posted by visakh16

quote:
Originally posted by rudba

Thanks for reply. but its not fix how many char on prefix. it could be more than 3 chars too.

then try this

SELECT LEFT(Col1,CHARINDEX('[0-9]', Col1)-1) AS Prefix,COUNT(ID) AS RecCnt
FROM Table
GROUP BY LEFT(Col1,CHARINDEX('[0-9]', Col1)-1)

and if you want order by use this in order by instead

its giving error as

Msg 536, Level 16, State 5, Line 12
Invalid length parameter passed to the SUBSTRING function.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:39:28

Thanks thanks guys,
this is perfect job :
select t.id,t.col1 from
( select row_number() over( partition by substring(col1,1,charindex('0',col1)-1) order by substring(col1,1,charindex('0',col1)-1) ) as id , substring(col1,1,charindex('0',col1)-1)as col1
from @temp ) t
where t.id = 1

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-18 : 12:42:56

Welcome....My above solution works only when the number ( Content after prefix in col1 ) starts with '0'.Beware of it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:44:28

quote:
Originally posted by rudba

Thanks thanks guys,
this is perfect job :
select t.id,t.col1 from
( select row_number() over( partition by substring(col1,1,charindex('0',col1)-1) order by substring(col1,1,charindex('0',col1)-1) ) as id , substring(col1,1,charindex('0',col1)-1)as col1
from @temp ) t
where t.id = 1

does 0 be always first numeric value? if not,above wont work.

also the earlier code can be modified like this to avoid error


SELECT LEFT(Col1,CASE WHEN CHARINDEX('[0-9]', Col1)>0 THEN CHARINDEX('[0-9]', Col1) ELSE LEN(Col1)+1 END -1) AS Prefix,COUNT(ID) AS RecCnt
FROM Table
GROUP BY LEFT(Col1,CASE WHEN CHARINDEX('[0-9]', Col1)>0 THEN CHARINDEX('[0-9]', Col1) ELSE LEN(Col1)+1 END-1)

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:44:45

Thanks guys, and also how do i know the how many char prefix are there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:49:18

[code]SELECT PATINDEX('[0-9]%', Col1)-1 FROM Table[/code]

should give you the number of prefix characters

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 12:55:21

I get the list of Prefix group by but i have to know how many char prefix ony.
Take a look this:

ID Col1
1 Abc001
2 Xyz001
3 Abc002
4 Mno-001
5 xyz002
6 abc003
7 mno-002

The Output looks like this:
1 Abc (3 char prefix)
2 xyz (3 char prefix)
3 Mno- (4 char prefix)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:58:23

isnt it right? ABC so 3, Mno- 4,...
seems right to me

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 13:12:34

Well, just updates prifix on col2 no. of char prefix on col3 and rest of value on col4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 13:14:11

quote:
Originally posted by rudba

Well, just updates prifix on col2 no. of char prefix on col3 and rest of value on col4

sorry didnt understand what you're telling. can you elaborate with some sample data?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 13:21:12

Okay,
I have data like this:
ID Col1
1 Abc001
2 Xyz001
3 Abc002
4 Mno-001
5 xyz002
6 abc003
7 mno-002

Now i have to update col2 on prefix ony, col3, char of no. prefix and col4 number only(after prefix)
Out put looks like this:

ID Col1 Col2 Col3 Col4
1 Abc001 Abc 3 001
2 Xyz001 Xyz 3 001
3 Abc002 Abc 3 002
4 Mno-001 Mno- 4 001
5 xyz002 xyz 3 002
6 abc003 abc 3 003
7 mno-002 mno- 4 002

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources