Like operator on a column name in SQL Server


To: [email protected]Subject: Help...Urgent
Hi Prabhu,

i need urgent help from you for sql script.
i have field called "Notes" in table ABC and have another table called XYZ.
XYZ table has five fields 1,2,3,4,5
the requirement is :

select * from abc
where 1=1
and abc.notes like '%xyz.1%'
and abc.notes like '%xyz.2%'
and abc.notes like '%xyz.3%'
and abc.notes like '%xyz.4%'
and abc.notes like '%xyz.5%'

did you understand?
notes field has some long strings... xyz table clumns string should match with that notes from the abc table.
how do i write the sql script.
becoz i have nearly 2000 records in the table.. can you help out plz....
its urgent.
thanks in advance
pinky


----------------------------------------------------------------------------------

It's a very usual question. How to use "Like operator on a column" -> We should use inner join on TableA.Column_Name like TableB.Column_Name


Let's see a small example to understand a little bit.


DROP TABLE PINKY1
DROP TABLE PINKY2

GO
CREATE TABLE PINKY1(NOTES VARCHAR(100))
INSERT INTO PINKY1 VALUES('VENKAT')
INSERT INTO PINKY1 VALUES('VENKATESAN PRABU')
INSERT INTO PINKY1 VALUES('rENKAT')
INSERT INTO PINKY1 VALUES('AENKAT')
INSERT INTO PINKY1 VALUES('AENKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('zxNKAT')
INSERT INTO PINKY1 VALUES('VERESEAEz')

GO
CREATE TABLE PINKY2(VAL1 VARCHAR(100),VAL2 VARCHAR(100),VAL3 VARCHAR(100),VAL4 VARCHAR(100),VAL5 VARCHAR(100))
INSERT INTO PINKY2 VALUES('VE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('BE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('SE%','%RE%','%SE%','%AE%','%E_')
INSERT INTO PINKY2 VALUES('DE%','%RE%','%SE%','%AE%','%E_')

GO

SELECT * FROM PINKY1 INNER JOIN PINKY2 ON NOTES LIKE VAL1
WHERE NOTES LIKE VAL2 AND NOTES LIKE VAL3 AND NOTES LIKE VAL4 AND NOTES LIKE VAL5

 

 
Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/