1

I have 2 tables

table1 ( id int, name nvarchar)
table2 ( pid int, parameters nvarchar)

parameters in table2 contains table1.id in nvarchar format, like "some text 12345 some text", where 12345 is the id which is contained in table1. Now I need to find all the table1.id s which are mentionioned in table2.parameters

So for my script is this, but this does not work

select id, name 
from table1 r
where exists
(
select [Parameters] from table2
where [Parameters] like cast(r.id as nvarchar(15))
)

I can't use % with like but I need it. What is the way I can do it?

  • 2
    Don't use this schema to store parameters (with free text), use a proper table with ParameterID, Value so you don't have this parsing nightmares.You will need to find a pattern that can match on your strings so you can subtract your ID from the 2nd table. The pattern or the expression will greatly depend on the values you have so include as many representative examples as you have. – EzLo Mar 5 '19 at 10:15
  • 1
    I can't use % with like WHERE CHARINDEX(CAST(table1.id AS VARCHAR), table2.parameters) > 0 – Akina Mar 5 '19 at 11:01
  • 1
    Why can't you use % with like? You can try splitting the spaces using STRING_SPLIT() if the parameters are always separated with spaces. – Randi Vertongen Mar 5 '19 at 11:39
  • Thanks for your support, it really helped! – igelr Mar 5 '19 at 11:43
  • 1
    they do not have common column O_O FROM table1 JOIN table2 ON CHARINDEX(CAST(table1.id AS VARCHAR), table2.parameters) > 0... or FROM table1, table2 WHERE CHARINDEX(CAST(table1.id AS VARCHAR), table2.parameters) > 0 – Akina Mar 5 '19 at 12:54
2

Yes, you can use % with LIKE. Since you are converting the INT "id" into a string, you can then concatenate the % character on both sides. But you also need to include a space separating each of them from the converted "id" so that you don't have an "id" of 23 matching a parameters value of something like "abc 1234 def". And yes, you can JOIN the tables since that is just a different way of expressing the same thing that you are already attempting.

SETUP

-- DROP TABLE #table1;
CREATE TABLE #table1 ( [id] INT NOT NULL PRIMARY KEY, [name] NVARCHAR(30));
-- DROP TABLE #table2;
CREATE TABLE #table2 ( [pid] INT NOT NULL PRIMARY KEY, [parameters] NVARCHAR(500));

SET NOCOUNT ON;
INSERT INTO #table1 ([id], [name]) VALUES (1, N'a');
INSERT INTO #table1 ([id], [name]) VALUES (2, N'b');
INSERT INTO #table1 ([id], [name]) VALUES (3, N'c');
INSERT INTO #table1 ([id], [name]) VALUES (12, N'ab');
INSERT INTO #table1 ([id], [name]) VALUES (23, N'bc');
INSERT INTO #table1 ([id], [name]) VALUES (123, N'abc');


INSERT INTO #table2 ([pid], [parameters]) VALUES (1, N'some text 12345 some text');
INSERT INTO #table2 ([pid], [parameters]) VALUES (2, N'DD 2 DD');
INSERT INTO #table2 ([pid], [parameters]) VALUES (3, N'EEE EE 123 EEEEE');
INSERT INTO #table2 ([pid], [parameters]) VALUES (4, N'NO23 12NO');
INSERT INTO #table2 ([pid], [parameters]) VALUES (5, N'HHHHHH YYY 23 ZZZZZ');
INSERT INTO #table2 ([pid], [parameters]) VALUES (6, N'QQQQQ r2q RRRRR');

CORRELATED SUBQUERY

SELECT r.[id], r.[name]
FROM #table1 r
WHERE EXISTS
(
  SELECT *
  FROM #table2 tmp
  WHERE tmp.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), r.[id]) + N' %'
);
/*
id     name
2      b
23     bc
123    abc
*/

JOIN

SELECT t1.[id], t1.[name], t2.[pid]
FROM #table1 t1
INNER JOIN #table2 t2
        ON t2.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), t1.[id]) + N' %';
/*
id     name    pid
2      b       2
23     bc      5
123    abc     3
*/
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.