CODEWITHSUNDEEP
sql-serverregex
refresh
refresh

Reputation: 1329

Search for row where email contains only the X character

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 [email protected] 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

Upvotes: 0

Views: 334

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44356

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 <> ''

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

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'),('[email protected]'),(NULL),('')) T(Email)
)
SELECT *
FROM STUDENTS
WHERE PATINDEX('%[^X]%', Email)=0 AND LEN(Email)>0

Upvotes: 1

Cato
Cato

Reputation: 3701

Try below query:

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

Upvotes: 1

Related Questions