Posted by matthewf_boi on 03/09/07 21:03

On Mar 9, 12:38 pm, "Chris" <cjscu...@gmail.com> wrote:
> 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', 'Bob@bob.com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('2', 'Bob.com')
INSERT INTO MYTABLE (COL1, COL2) VALUES ('3', 'mary@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

[Back to original message]