I'm trying to join a table onto another table. The gimmick here is that the column from the table contains a long string. Something like this:

   PageNumber-190-ChapterTitle-HelloThere
   PageNumber-19-ChapterTitle-NotToday

I have another table that has a list of page numbers and whether or not I want to keep those pages, for example:

I want to be able to return a query that contains the long string but only if the page number exists somewhere in the string. The problem I have is that, when using a LIKE statement to join:

JOIN t2 ON t1.string LIKE '%'   t2.page_number   '%' WHERE keep_flag = 'Y'

It will still return both results for whatever reason. The column of "Keep Flag" in the results query will change to "Y" for page 19 even though it shouldn't be in the results.

I obviously don't think LIKE is the best way to JOIN given that '19' is LIKE '190'. What else can I do here?

CodePudding user response：

Please try the following solution.

It is doing JOIN on the exact match.

SQL

-- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT @tbl1 (tokens) VALUES
('PageNumber-190-ChapterTitle-HelloThere'),
('PageNumber-19-ChapterTitle-NotToday');

DECLARE @tbl2 TABLE (ID INT IDENTITY PRIMARY KEY, Page_Number INT, Keep_Flag CHAR(1));
INSERT @tbl2 (Page_Number, Keep_Flag) VALUES
(190, 'Y'),
(19 , 'N');
-- DDL and sample data population, end

SELECT * 
FROM @tbl1 AS t1 INNER JOIN @tbl2 AS t2
        ON PARSENAME(REPLACE(t1.tokens,'-','.'),3) = t2.Page_Number
WHERE t2.keep_flag = 'Y';

Output

CodePudding user response：

if Page_number is a number you must cats it as varchar, so that the types fit.

You can read on thehome page ,ot about cast and convert see https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

CREATE TABLE tab1
    ([str] varchar(38))
;
    
INSERT INTO tab1
    ([str])
VALUES
    ('PageNumber-190-ChapterTitle-HelloThere'),
    ('PageNumber-19-ChapterTitle-NotToday')
;

2 rows affected

CREATE TABLE tab2
    ([Page Number] int, [Keep Flag] varchar(1))
;
    
INSERT INTO tab2
    ([Page Number], [Keep Flag])
VALUES
    (190, 'Y'),
    (19, 'N')
;

2 rows affected

SELECT str
FROM tab1 JOIN tab2 ON tab1.str LIKE '%'   CAST(tab2.[Page Number]AS varchar)   '%' AND tab2.[Keep Flag] = 'Y'

fiddle