Select question

[Chris]

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

[matthewf_boi]

On Mar 9, 12:38 pm, "Chris" <[e-mail]:

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

CREATE TABLE MYTABLE (COL1 VARCHAR(80), COL2 VARCHAR(80))

INSERT INTO MYTABLE (COL1, COL2) VALUES ('1', 'B**@bob.com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('2', 'Bob.com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('3', 'm***@bob.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

[Erland Sommarskog]

Chris (cj******@gmail.com) writes:

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.

A second alternative to Matthew's:

SELECT ... FROM tbl WHERE col NOT LIKE '%@%'
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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