Replace string while updating sql

Question

I have table called Empdetails. Here one of column name is loginid. I want to replace loginid if it contain @s.com, in few case it has.

Loginid
[email protected]
sdf
ghj
[email protected]
[email protected]
[email protected]

update  Empdetails
set loginid = REPLACE(loginid, '@s.com', '')
where id in (1,6,8,9)

If I mistakenly mention id whose loginid does not contain any '@s.com', will throw an error. Is above query is fine

You can add AND loginid LIKE '%@s.com%' to the WHERE clause to keep transaction size down. — jarlh
– jarlh, Commented Mar 20, 2019 at 8:51
Please edit your question and add the exact error message you get. — user330315
– user330315, Commented Mar 20, 2019 at 8:53

Sudipta Mondal · Accepted Answer · 2019-03-20 08:54:40Z

3

To answer your question, your query will not end in Error.

The below will only update records which end with '@s.com'

update Empdetails
set loginid = REPLACE(loginid, '@s.com', '')
where loginid  like '%@s.com'

You don't need to specify ID, you can just use your logic to filter records which you want to update

answered Mar 20, 2019 at 8:52

Sudipta Mondal

2,5721 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maybe he wants to replace them in that specifics ids. Upvoted anyway

@Cid edited to just answer the OP's question. Also OP mentions - I want to replace loginid if it contain @s.com

Thanks @cid,@sujipta