SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 group by part of string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/27/2013 :  06:49:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/27/2013 :  07:11:29  Show Profile  Reply with Quote

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


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

Edited by - visakh16 on 05/27/2013 07:14:58
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 05/27/2013 :  07:21:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/27/2013 :  07:26:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/27/2013 :  07:30:20  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/27/2013 :  09:15:24  Show Profile  Reply with Quote
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
)
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/27/2013 :  21:59:39  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 05/28/2013 :  00:10:07  Show Profile  Reply with Quote
Have you tried JamesK Solution (Posted - 05/27/2013 : 09:15:24)?
I think that is suitable for your requirement

--
Chandu
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/28/2013 :  01:45:35  Show Profile  Reply with Quote
yea, james solution more "neat" hahaa
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  01:58:53  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/28/2013 :  05:04:39  Show Profile  Reply with Quote
for this?

declare @table table(col1 varchar(10))
insert into @table values ('abc'),('123'),('abc123'),('def'),('f'),('def')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  05:08:05  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/28/2013 :  10:30:12  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000