question

innovator avatar image
innovator asked

Dump data from two tables into new table with search like operation

Hi All, I wanna extract data from two tables table1(product_code varchar(20),name varchar(20)) and table2(desc varchar(20),desc_id int) in such a way that the suffix part of product_code column of table1 matches against the desc column of table2 for ex-> Data is Table1 '123CPN','Hero' '323CPN','Ram' '234PQR','POL' '234PQR','PPP' Table2 'CPN','1' 'PQR','2' I wanna data i the form as below 'Hero','CPN', 'Ram','CPN', 'POL','PQR', 'PPP','PQR' Thanx in advance
joinsearch
12 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thnx Dave for immediate response,But the Product_Code columns values which varies dynamically like '123CPN' can become '123CPNR',So I wanna match this "CPNR" part with desc column of table2 and then retreive the result.Hope u gor my concern!!!
0 Likes 0 ·
Ah ok, so will it always be on the end though?
0 Likes 0 ·
If it is you could do something like: select T1.Name, T2.Desc from Table1 as T1 Join Table2 as T2 on Right(T1.Product_Code,LEN(T2.Desc)) = T2.Desc However USMans might preform better, not sure as I've not used patindex in that way before
0 Likes 0 ·
Ahhh...performance wise I do not like TSQL for string comparisons :) But PATINDEX "seems" to be more relevant to this example.
0 Likes 0 ·
To clarify further, For ex if I have data. 'CPNR123' 'CPNT125' 'CPN4568' So I wanna check all characters after 'CPN' in above strings whether they are numeric or not. Output should be 'CPN4568' from above scenario. Any help in this regard would be much appreciated!!
0 Likes 0 ·
Show more comments
Usman Butt avatar image
Usman Butt answered

Use PATINDEX for that. Something like

DECLARE @table1 TABLE
(
product_code VARCHAR(20),
name VARCHAR(20) 
)

DECLARE @table2 TABLE
(
[desc] VARCHAR(20),
desc_id INT
)

INSERT @table1
SELECT '123CPN','Hero'
UNION ALL SELECT '323CPN','Ram'
UNION ALL SELECT '234PQR','POL'
UNION ALL SELECT '234PQR','PPP'
UNION ALL SELECT 'CPNR234','PPP'


INSERT @table2
SELECT 'CPN','1'
UNION ALL SELECT 'PQR','2'
UNION ALL SELECT 'CPNR','3'


    SELECT [product_code]
    ,       [name]
    ,       [desc]
    ,       [desc_id]
    ,       CASE WHEN PATINDEX('%'+t2.[DESC]+'[0-9]%', t1.product_code ) > 0 THEN 
            SUBSTRING([t1].[product_code], PATINDEX('%'+t2.[DESC]+'%', t1.product_code ), LEN([t1].[product_code]))
            ELSE [desc] END AS StringNeeded
    FROM @Table1 t1 INNER JOIN @Table2 t2
    ON PATINDEX('%'+t2.[DESC]+'[^A-Z]%', t1.product_code ) > 0 
    OR PATINDEX('%'+t2.[DESC], t1.product_code ) > 0
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thnx a lot Usman! U r great!!
0 Likes 0 ·
One more thing Usman!! If I have a string 'CPN1234' and I wanna check whther any character after 'CPN' in above string is numeric or not, In such a case , I wanna extract any character that comes after 'CPN' to check whether its numeric or not,So what u reckon on said scenario?
0 Likes 0 ·
Dave Morrison avatar image
Dave Morrison answered
Hi, If I've understood correctly try select T1.Name, T2.Desc from Table1 as T1 Join Table2 as T2 on Right(T1.Product_Code,3) = T2.Desc Thanks Dave
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.