Compare Two Columns with WildCard

I am trying to compare columns in two tables with a wildcard character.

One table: Other Table:
-------
ID Col1 | ID Col2
-------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
----------
I want to writing something like:

SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

which obviously does not work.

basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

1A LIKE '1*'
1B LIKE '1*'

How can I do a comparison like this?I am reminded of an old saying:
"Make it possible for programmers to write programs in English, and you will find that programmers cannot write in English."
Care to try that explanation again? Once more, with feeling...|||ha, sorry about that. my formatting is all messed up above too, i'm sure that didn't help either.

I basically just want to write a SELECT query and compare two columns with a wildcard character.

how do I do this?

do something like:

table1.col1 LIKE 'sam%'

except with another column like:

table1.col1 LIKE '(table2.col2)%'

except that doesn't work... can I do this?|||select Table1.ID
, Table1.Col1
, Table2.ID
, Table2.Col2
from Table1
left outer
join Table2
on Table2.ID = Table1.ID
and Table1.Col1 like Table2.Col2 + '%'|||create table #t1 (id int, c1 int)
insert into #t1 select
1, 1 union all select
2, 2 union all select
3, 3
create table #t2 (id int, c1 varchar(10))
insert into #t2 select
1, '1A' union all select
1, '1B' union all select
2, '8A' union all select
2, '8B' union all select
3, '3'

select * from #t1 a,#t2 b
where a.id=b.id
and b.c1 like convert(varchar(10),a.c1)+'%'

drop table #t1
drop table #t2

Compare tables

can any one suggested me a better way to compare two tables.
I tried following,
create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
insert into t1(col1,col2) values('c11','c12')
insert into t1(col1,col2) values('c21','c22')
insert into t1(col1,col2) values('c31','c32')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t2(col1,col2) values('c11','c12')
insert into t2(col1,col2) values('c21','c22')
insert into t2(col1,col2) values('c31','c32')
insert into t2(col1,col2) values('c41','c42')
select col1 from
(
select col1,col2 from
t1
union all
select col1,col2 from
t2
) a
group by col1,col2 having count(*)<>2skg,
"What" is it that you want to find out?
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
> can any one suggested me a better way to compare two tables.
> I tried following,
> create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> insert into t1(col1,col2) values('c11','c12')
> insert into t1(col1,col2) values('c21','c22')
> insert into t1(col1,col2) values('c31','c32')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
>
> insert into t2(col1,col2) values('c11','c12')
> insert into t2(col1,col2) values('c21','c22')
> insert into t2(col1,col2) values('c31','c32')
> insert into t2(col1,col2) values('c41','c42')
>
> select col1 from
> (
> select col1,col2 from
> t1
> union all
> select col1,col2 from
> t2
> ) a
> group by col1,col2 having count(*)<>2
>|||Since you do not have any keys and the columns are NULL-able, this is
probably as good as anything else.|||Thanks!!. I want to to know if both the tables have same rows of data. i.e
both tables are same.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
> skg,
> "What" is it that you want to find out?
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||skg,
You can start with something like this:
SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
and work it with IS NOT NULL etc.. depending on your needs and requirements.
A more flexible and robust solution would be to use a third-party software
package to make the changes if required. See (as an example):
http://www.red-gate.com/products/SQ...mpare/index.htm
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
> Thanks!!. I want to to know if both the tables have same rows of data. i.e
> both tables are same.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!! Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> skg,
> You can start with something like this:
> SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
> and work it with IS NOT NULL etc.. depending on your needs and
> requirements.
> A more flexible and robust solution would be to use a third-party software
> package to make the changes if required. See (as an example):
> http://www.red-gate.com/products/SQ...mpare/index.htm
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
>|||skg
SELECT a.col1,a.col2,b.col1,b.col2
From (Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t1 ) a
Inner Join (
Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t2 ) b
On a.col1 = b.col1 and a.col2 = b.col2
Where a.CheckSum = b.CheckSum
"skg" <skg@.yahoo.com> wrote in message
news:umyMn5dzFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks!!! Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
>