Search for row where email contains only the X character

Question

I want to search for rows whose column email has only the X character.

For example, if the email is XXXX, it should fetch the row but if the email is XXX@XXX.COM it should not be fetched.

I have tried something like this, but it is returning me all emails having the character X in it:

select *
from STUDENTS
where EMAIL like '%[X+]%';

Any idea what is wrong with my query?

Thanks

Have a look at:https://stackoverflow.com/a/27569954/7124761 – Prashant Pimpale Oct 11 '18 at 12:43 — Prashant Pimpale, Oct 11 '18 at 12:43

score 1 · Accepted Answer · edited Oct 11 '18 at 12:42

1

Try below query:

select *
from STUDENTS
where LEN(EMAIL) > 0 AND LEN(REPLACE(EMAIL,'X','')) = 0;

edited Oct 11 '18 at 12:42

Prashant Pimpale

10,349
9
44
84

answered Oct 11 '18 at 12:41

Cato

3,652
9
12

Paweł Dyl · Answer 2 · 2018-10-11T12:51:26.780

1

I would use PATINDEX:

SELECT * FROM STUDENTS WHERE PATINDEX('%[^X]%', Email)=0

Only X means no other characters than X.

To handle NULLs and empty strings you should consider additional conditions. See demo below:

WITH STUDENTS AS
(
    SELECT * FROM (VALUES ('XXXX'),('XXX@XXX.COM'),(NULL),('')) T(Email)
)
SELECT *
FROM STUDENTS
WHERE PATINDEX('%[^X]%', Email)=0 AND LEN(Email)>0

edited Oct 11 '18 at 12:51

answered Oct 11 '18 at 12:44

Paweł Dyl

8,888
1
11
27

I don't think patindex bettter than regex – t-clausen.dk Oct 11 '18 at 16:05

t-clausen.dk · Answer 3 · 2018-10-11T15:44:13.840

0

This will find all rows where email only contain 1 or more X and no other characters.

SELECT *
FROM STUDENTS
WHERE Email not like '%[^X]%' and Email <> ''

edited Oct 11 '18 at 15:44

answered Oct 11 '18 at 13:04

t-clausen.dk

43,517
12
59
92

Could you elaborate on how this solves the OP's problem? – Reinstate Monica Oct 11 '18 at 15:20

Search for row where email contains only the X character

3 Answers3