Can some one help me to write select statement to retrive some data from table using Below conditions.
Select Field1, Field2
From Table
Where Field3 = 'Temp' and
Field4 contains PASSWORD, P/WORD, PASSWD, PASSWRD, PWORD, P\WORD, PSWRD, ####, ****, P/W, P/ WORD, P / WORD, P /WORD, P\ WORD, P \ WORD, P \WORD,
Here Field4 is 200 bytes and it contains memo's Text and i want retrive the Field1 and Field2 data if Field4 Text contains any of the strings above.
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
By calling the field "memo Text" I jumped to thinking this is MS Access where they don't use CHAR/VARCHAR/BLOB, but rather TEXT and MEMO.
Too bad SQL doesn't understand regular expressions so well. Please correct me if I am wrong on that. Then you could just do a match of the field to a regex.
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
venkatarao wrote:
it is not worked out.
May be because of Field4 is defined as Varchar in my table.
Try this to look for a substring within a string.
Code:
SELECT FIELD1, FIELD2
FROM TABLE
WHERE FIELD3 = 'TEMP'
AND (POSSTR(FIELD4,'PASSWORD') <> 0
OR POSSTR(FIELD4,'P/WORD') <> 0
OR POSSTR(FIELD4,'PASSWD') <> 0
OR POSSTR(FIELD4,'PASSWRD') <> 0
OR POSSTR(FIELD4,'####') <> 0
OR POSSTR(FIELD4,'P/WORD') <> 0
OR POSSTR(FIELD4,'P/W') <> 0
OR POSSTR(FIELD4,'P/ WORD') <> 0
OR POSSTR(FIELD4,'P / WORD') <> 0
OR POSSTR(FIELD4,'P /WORD') <> 0
OR POSSTR(FIELD4,'P \WORD') <> 0
OR POSSTR(FIELD4,'P\ WORD') <> 0
OR POSSTR(FIELD4,'P \ WORD') <> 0
OR POSSTR(FIELD4,'P \WORD') <> 0)