Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
I want to return all Table One.id which appear in table Two.id by comparing
the varchar column e.g
select One.id from One, Two
where One.id like (Two.id + '%')
Can someone show me a better way. ThanksMaybe this?
select * from one where id in (select id from two)
Ben Nevarez
"mtv" <mtv@.discussions.microsoft.com> wrote in message
news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
> Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
> Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
> I want to return all Table One.id which appear in table Two.id by
> comparing
> the varchar column e.g
> select One.id from One, Two
> where One.id like (Two.id + '%')
> Can someone show me a better way. Thanks|||Thank you Ben but I believe it will not work. Ids in table Two has one
additional letter in the end.
cheers
mtv
"Ben Nevarez" wrote:
> Maybe this?
> select * from one where id in (select id from two)
> Ben Nevarez
>
> "mtv" <mtv@.discussions.microsoft.com> wrote in message
> news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
>
>|||Would something like this help?
=====
CREATE TABLE test1
(
colA VARCHAR(10)
)
GO
CREATE TABLE test2
(
colB VARCHAR(10)
)
GO
INSERT INTO test1 SELECT 'AAABBBCCC'
INSERT INTO test1 SELECT 'XXXYYYZZZ'
INSERT INTO test2 SELECT 'AAABBBCCCD'
GO
SELECT
test1.colA, test2.colB
FROM
test1
INNER JOIN test2 ON test1.colA = LEFT (test2.colB, 9)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"mtv" <mtv@.discussions.microsoft.com> wrote in message
news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
> Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
> Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
> I want to return all Table One.id which appear in table Two.id by
> comparing
> the varchar column e.g
> select One.id from One, Two
> where One.id like (Two.id + '%')
> Can someone show me a better way. Thanks
Friday, February 10, 2012
comparing two varchar variable
Labels:
aaa-bbb-ccc,
aaa-bbb-ccci,
aaa-bbb-ddd,
column,
comparing,
database,
microsoft,
mysql,
oracle,
server,
sql,
table,
varchar,
variable
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment