Find rows where column1 matches column2 (possibly in an another row)
Say I have some code below from a table called Table1.
Column1 | Column2
41391175 | 41000
41523664 | 41523
110505116 | 110509
110453629 | 110505
41000 | 351592
Column1 and Column2 are NVARCHAR(10). What I want to return is all entries in Column 1 where Column2 is IN Column1, so I want a result to look like this-
Column1
41523664
110505116
41000
Right now, this is what my code would look like-
SELECT Column1
FROM Table1
INTERSECT
SELECT Column2
FROM Table1 ;
But as of right now, that only finds the EXACT same number as in Column2, rather than one that Column1 contains inside it, so I would just get this result-
Column1
41000
Is there a way to work around this, or to get an IN clause within INTERSECT? I haven't been able to find something that does that after some research.
sql
sql-server intersect edited Nov 26 '18 at 12:00
Salman A
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
add a comment |
Say I have some code below from a table called Table1.
Column1 | Column2
41391175 | 41000
41523664 | 41523
110505116 | 110509
110453629 | 110505
41000 | 351592
Column1 and Column2 are NVARCHAR(10). What I want to return is all entries in Column 1 where Column2 is IN Column1, so I want a result to look like this-
Column1
41523664
110505116
41000
Right now, this is what my code would look like-
SELECT Column1
FROM Table1
INTERSECT
SELECT Column2
FROM Table1 ;
But as of right now, that only finds the EXACT same number as in Column2, rather than one that Column1 contains inside it, so I would just get this result-
Column1
41000
Is there a way to work around this, or to get an IN clause within INTERSECT? I haven't been able to find something that does that after some research.
sql
sql-server intersect edited Nov 26 '18 at 12:00
Salman A
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03
add a comment |
Say I have some code below from a table called Table1.
Column1 | Column2
41391175 | 41000
41523664 | 41523
110505116 | 110509
110453629 | 110505
41000 | 351592
Column1 and Column2 are NVARCHAR(10). What I want to return is all entries in Column 1 where Column2 is IN Column1, so I want a result to look like this-
Column1
41523664
110505116
41000
Right now, this is what my code would look like-
SELECT Column1
FROM Table1
INTERSECT
SELECT Column2
FROM Table1 ;
But as of right now, that only finds the EXACT same number as in Column2, rather than one that Column1 contains inside it, so I would just get this result-
Column1
41000
Is there a way to work around this, or to get an IN clause within INTERSECT? I haven't been able to find something that does that after some research.
sql
sql-server intersect edited Nov 26 '18 at 12:00
Salman A
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
Say I have some code below from a table called Table1.
Column1 | Column2
41391175 | 41000
41523664 | 41523
110505116 | 110509
110453629 | 110505
41000 | 351592
Column1 and Column2 are NVARCHAR(10). What I want to return is all entries in Column 1 where Column2 is IN Column1, so I want a result to look like this-
Column1
41523664
110505116
41000
Right now, this is what my code would look like-
SELECT Column1
FROM Table1
INTERSECT
SELECT Column2
FROM Table1 ;
But as of right now, that only finds the EXACT same number as in Column2, rather than one that Column1 contains inside it, so I would just get this result-
Column1
41000
Is there a way to work around this, or to get an IN clause within INTERSECT? I haven't been able to find something that does that after some research.
sql
sql-server intersect sql
sql-server intersect edited Nov 26 '18 at 12:00
Salman A
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
edited Nov 26 '18 at 12:00
Salman A
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
edited Nov 26 '18 at 12:00
Salman A
181k66339431
edited Nov 26 '18 at 12:00
Salman A
181k66339431
edited Nov 26 '18 at 12:00
Salman A
181k66339431
181k66339431
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
asked Nov 14 '18 at 16:47
Sum 182Sum 182
31
31
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03
add a comment |
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03
add a comment |
2 Answers
2
active
oldest
votes
I suppose you can match them using LIKE operator:
SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
SELECT 1
FROM Table1 AS x
WHERE t.Column1 LIKE x.Column2 + '%'
)
Demo on DB Fiddle
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
add a comment |
select c1.column1 from table1 c1 inner join (select distinct column2 from table1) c2 on charindex(c2.column2, c1.column1) = 1
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305085%2ffind-rows-where-column1-matches-column2-possibly-in-an-another-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I suppose you can match them using LIKE operator:
SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
SELECT 1
FROM Table1 AS x
WHERE t.Column1 LIKE x.Column2 + '%'
)
Demo on DB Fiddle
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
add a comment |
I suppose you can match them using LIKE operator:
SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
SELECT 1
FROM Table1 AS x
WHERE t.Column1 LIKE x.Column2 + '%'
)
Demo on DB Fiddle
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
add a comment |
I suppose you can match them using LIKE operator:
SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
SELECT 1
FROM Table1 AS x
WHERE t.Column1 LIKE x.Column2 + '%'
)
Demo on DB Fiddle
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
I suppose you can match them using LIKE operator:
SELECT Column1
FROM Table1 AS t
WHERE EXISTS (
SELECT 1
FROM Table1 AS x
WHERE t.Column1 LIKE x.Column2 + '%'
)
Demo on DB Fiddle
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
answered Nov 14 '18 at 17:04
Salman ASalman A
181k66339431
181k66339431
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
add a comment |
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
It worked! Thank you so much. :)
– Sum 182
Nov 14 '18 at 17:25
add a comment |
select c1.column1 from table1 c1 inner join (select distinct column2 from table1) c2 on charindex(c2.column2, c1.column1) = 1
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
add a comment |
select c1.column1 from table1 c1 inner join (select distinct column2 from table1) c2 on charindex(c2.column2, c1.column1) = 1
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
add a comment |
select c1.column1 from table1 c1 inner join (select distinct column2 from table1) c2 on charindex(c2.column2, c1.column1) = 1
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
select c1.column1 from table1 c1 inner join (select distinct column2 from table1) c2 on charindex(c2.column2, c1.column1) = 1
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
answered Nov 14 '18 at 23:03
Philip TinneyPhilip Tinney
1,8361719
1,8361719
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305085%2ffind-rows-where-column1-matches-column2-possibly-in-an-another-row%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What are the datatypes of column1 and 2?
– Salman A
Nov 14 '18 at 17:00
Column 1 has the entry 41523664 and Column 2 has 41523, so because 41523 is IN 41523664(in this case, it's the first five digits), I want 41523664 to return. Does that make sense? Let me know if it is still not clearly seen in the post.
– Sum 182
Nov 14 '18 at 17:01
Both are nvarchar(10)
– Sum 182
Nov 14 '18 at 17:03