Is there a way to select records that do not contain a certain
character string in char, vchar or text fields? For example records
where the email field does not contain the "@" character. Can't find
it in the BoL.

Chris

On Mar 9, 12:38 pm, "Chris" <cjscu... (AT) gmail (DOT) com> wrote:
CREATE TABLE MYTABLE (COL1 VARCHAR(80), COL2 VARCHAR(80))

INSERT INTO MYTABLE (COL1, COL2) VALUES ('1', 'Bob (AT) bob (DOT) com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('2', 'Bob.com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('3', 'mary (AT) bob (DOT) com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('4', 'Mary')

SELECT * FROM MYTABLE WHERE CHARINDEX('@', COL2) =0

Charindex is looking for the position # of the character specified.
If the character is not there, the charindex should be zero.

HTH
Matthew

Chris (cjscully (AT) gmail (DOT) com) writes:
A second alternative to Matthew's:

SELECT ... FROM tbl WHERE col NOT LIKE '%@%'


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx