CODEWITHSUNDEEP
sqlsql-serverpattern-matching
Amit R
Amit R

Reputation: 101

Pattern Matching In SQL where characters appears anywhere but in sequence

I'm attempting to write a query to fix invalid email formats. The invalid email format I am getting is of the form first@name_company@com.

The characters always appear as '@' '_' '@' in that sequence.

If this is the format then I am replacing @ with . and _ with @ to get a correct format as:

[email protected]

I am considering a case condition like this:

When @email like '[a-z,0-9,_,-,@]_[a-z,0-9,_,-@]'  THEN replace(replace(replace(@email,'@','.'),'_','@'))

This only seems to work if I put "_" as a wildcard like this '[a-z,0-9,_,-,@]%_%[a-z,0-9,_,-@]'

Upvotes: 2

Views: 47

Answers (3)

Amit R
Amit R

Reputation: 101

Thanks Everyone. I used the following and it seems to be working:

case when charindex('@',@email,patindex ('%@%',@email)+1) - (patindex ('%@%',@email)+1)>1 and 

substring(@email,
     patindex ('%@%',@email)+1,

     charindex('@',@email,patindex ('%@%',@email)+1) - (patindex ('%@%',@email)+1)

     ) like '%_%'

Upvotes: 0

Jen R
Jen R

Reputation: 1535

I found two ways to do this using your specific example.

create table #myTemp
(someText varchar(50))

insert into #myTemp
values
('first@name_company@com')

select 
    -- check based on length after replacing the characters with nothing
    case when len(someText) - len(replace(replace(someText,'@',''),'_','')) = 3 
    then replace(replace(someText,'@','.'),'_','@')
    end as LengthCheck,
    -- check the position of @ to the left and right of _
    case when charindex('@',(Right(someText, CHARINDEX('_', reverse(someText))-1))) > 0
        and charindex('@',(LEFT(someText, CHARINDEX('_', someText)))) > 0
    then replace(replace(someText,'@','.'),'_','@')
    end as CharCheck
from #myTemp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270351

SQL Server is quite limited in pattern matching, but you can do something like this:

where email like '%_@_%' and      -- @ with something before and after
      email not like '%@%@%' and  -- not two @s
      email not like '%[^[email protected]]%'  -- has no obvious bad characters

This is not a guarantee of a good email, but it catches many problems.

Upvotes: 0

Related Questions