Select substring entries from another table

Question

I have two tables:

data    
id   |email    
_   
1    |[e-mail]    
2    |[e-mail]    
3    |zzzgimail.com 

errors    
_    
error    |correct    
@gmial.com|@gmail.com    
gimail.com|@gmail.com    

How can I select from dataall records with an email error? Thank.

Answer 1

SELECT d.id, d.email
FROM data d
    INNER JOIN errors e ON d.email LIKE '%' + e.error

Would do this, however, doing LIKE with a wildcard at the beginning of the matched value would prevent the use of the index so you can see poor performance.

The best approach would be to define a computed column in the data table, i.e. REVERSE the email field and index it. This would turn the above query into a LIKE condition with a wildcard at the end like this:

SELECT d.id, d.email
FROM data d
    INNER JOIN errors e ON d.emailreversed LIKE REVERSE(e.error) + '%'

, .

.

Answer 2

, :

declare @data table (
    id int,
    email varchar(100)
)

insert into @data
    (id, email)
    select 1, '[e-mail]' union all
    select 2, '[e-mail]' union all
    select 3, 'zzzgimail.com'

declare @errors table (
    error varchar(100),
    correct varchar(100)
)

insert into @errors
    (error, correct)
    select '@gmial.com', '@gmail.com' union all
    select 'gimail.com', '@gmail.com'   

select d.id, 
       d.email, 
       isnull(replace(d.email, e.error, e.correct), d.email) as CorrectedEmail
    from @data d
        left join @errors e
            on right(d.email, LEN(e.error)) = e.error

Answer 3

, , .

SQL "" . , .

"" SQL- SQL Server , SQL (IMO) .

Answer 4

select * from 
(select 1 as id, '[e-mail]' as email union
 select 2 as id, '[e-mail]' as email union
 select 3 as id, 'zzzgimail.com' as email) data join

(select '@gmial.com' as error, '@gmail.com' as correct union
 select 'gimail.com' as error, '@gmail.com' as correct ) errors

 on data.email like '%' + error + '%' 

... , , . , .