help with quary.. - 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)

help with quary..

Author

Topic

tom8080
Starting Member

3 Posts

Posted - 2010-08-08 : 09:57:52

I want to find all couples of values from single column that the only different between them is the tag in the end of the string.
For example: the couple (hello, hello') or (aaa,aaa')

Can someone help me write the query ?

10x !

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 10:02:31

please send structure of your table and some sample data.

tom8080
Starting Member

3 Posts

Posted - 2010-08-08 : 10:12:59

for example: single column table:

-----------------
| Data |
=================
| "aaaa" |
-----------------
| "aaaa'" |
-----------------
| "erty" |
-----------------
| "asdf" |
-----------------
| "asd8das" |
-----------------
| "hello'" |
-----------------
| "hello" |
-----------------

result:
"hello", "hello'"
"aaaa" , "aaaa'"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 10:39:51

try this:


--data
declare @temp table
(data varchar(10))

insert into @temp
select 'aaaa' union all
select 'aaaa''' union all
select 'erty' union all
select 'asdf' union all
select 'asd8das' union all
select 'hello''' union all
select 'hello' 

--solution
select 
x.data2 as combination1
,t.data  as combination2
from (
select 
replace(data,'''','') as data2
from @temp
group by replace(data,'''','') 
having count(replace(data,'''','')) = 2
) as x
 cross join @temp as t
 where
	t.data like  x.data2 + '%'
and len(t.data) > len(x.data2)

tom8080
Starting Member

3 Posts

Posted - 2010-08-08 : 11:31:46

that work !
thanks alot !

another option that i find:

SELECT t1.data as d1, t2.data as d2 FROM @temp as t1, @temp as t2
WHERE t1.data = t2.data + ''''

SELECT Girls WHERE Sun is shine and sea is blue

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 12:05:18

nice one. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources