group by part of string - 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

group by part of string

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-27 : 06:49:10

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f')

output
-------
abc123
def

why only this 2?
abc123 is built from abc, 123 and abc123
def is built from f, def

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:11:29

[code]
SELECT t.col1
FROM @table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM @table
WHERE t.col1 LIKE col1 + '%'
AND LEN(t.col1) > LEN(col1)
)t1
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM @table
WHERE col1 LIKE t1.col1 + '%'
AND LEN(col1) > LEN(t1.col1)
)t2
WHERE t1.Cnt > 0
AND COALESCE(t2.Cnt,0)=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 07:21:00

I think this is enough

SELECT DISTINCT t.col1
FROM @table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM @table
             WHERE t.col1  LIKE '%'+ col1 + '%'
             AND LEN(t.col1) > LEN(col1)
             )t1
WHERE Cnt > 0

@Visakh --> In OUTER APPLY part t1 doesn't have column col1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:26:03

it should be t


SELECT t.col1
FROM @table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM @table
             WHERE t.col1  LIKE col1 + '%'
             AND LEN(t.col1) > LEN(col1)
             )t1
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM @table
             WHERE col1  LIKE t1.col1 + '%'
             AND LEN(col1) > LEN(t.col1)
             )t2
WHERE t1.Cnt > 0
AND COALESCE(t2.Cnt,0)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:30:20

quote:

Originally posted by bandi

I think this is enough

SELECT DISTINCT t.col1
FROM @table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
             FROM @table
             WHERE t.col1  LIKE '%'+ col1 + '%'
             AND LEN(t.col1) > LEN(col1)
             )t1
WHERE Cnt > 0

@Visakh --> In OUTER APPLY part t1 doesn't have column col1

--
Chandu

not sure on that

for below sample data what should be the output?
let the OP confirm


123MACABT
123MACABTPQR
MAC
MACPRT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-27 : 09:15:24

quote:
Originally posted by waterduck

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f')

output
-------
abc123
def

why only this 2?
abc123 is built from abc, 123 and abc123
def is built from f, def

select distinct col1 from @table t1
where not exists
( 
	select * from @table t2 
	where t2.col1 like '%'+t1.col1+'%'
	and t2.col1 <> t1.col1
)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-27 : 21:59:39

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

I think this is enough
SELECT DISTINCT t.col1
FROM @table t
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM @table
WHERE t.col1 LIKE '%'+ col1 + '%'
AND LEN(t.col1) > LEN(col1)
)t1
WHERE Cnt > 0

@Visakh --> In OUTER APPLY part t1 doesn't have column col1

--
Chandu

not sure on that

for below sample data what should be the output?
let the OP confirm

123MACABT
123MACABTPQR
MAC
MACPRT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16,

input
123MACABT
123MACABTPQR
MAC
MACPRT

output
123MACABTPQR
MACPRT

however, thx all for giving me the idea...im stucked with using full join....haha

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-28 : 00:10:07

Have you tried JamesK Solution (Posted - 05/27/2013 : 09:15:24)?
I think that is suitable for your requirement

--
Chandu

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-28 : 01:45:35

yea, james solution more "neat" hahaa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 01:58:53

quote:
Originally posted by James K

quote:
Originally posted by waterduck

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f')

output
-------
abc123
def

why only this 2?
abc123 is built from abc, 123 and abc123
def is built from f, def
select distinct col1 from @table t1
where not exists
(
select * from @table t2
where t2.col1 like '%'+t1.col1+'%'
and t2.col1 <> t1.col1
)

Good one

b/w whats the distinct for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-28 : 05:04:39

for this?

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f'),('def')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 05:08:05

quote:
Originally posted by waterduck

for this?

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f'),('def')

oh
so you've duplicate values in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-28 : 10:30:12

quote:
Originally posted by waterduck

for this?

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f'),('def')

Yes, indeed. My guess was that you didn't want two instances of def returned.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources