MSSQL Replace string with another string after a certain character

Question

I am receiving a email id into my sql procedure.I need to replace the email client with a defined string.

Suppose I receive email id such as [email protected] or [email protected] or [email protected], in such cases I need to replace @gmail.com / @yahoo.com with a fixed string.

[email protected] - [email protected]
[email protected] - [email protected]
[email protected] - [email protected]

So the string after @ will be replaced with my defined string irrespective of the data received in procedure.

Tried Replace method but it didn't work. I know that i need to use charindex, substring & left functions to make it work but not getting the right combination.

Tim Biegeleisen · Accepted Answer · 2019-06-15 08:47:21Z

2

I would keep it simple here and just use CHARINDEX with LEFT:

UPDATE yourTable
SET email_id = LEFT(email_id, CHARINDEX('@', email_id) - 1) + 'outlook.com';

edited Jun 15, 2019 at 8:47

answered Jun 15, 2019 at 8:24

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

ROY Over a year ago

received error message as 'The left function requires 2 argument(s).'

Gordon Linoff Over a year ago

I would add WHERE email_id LIKE '%@%'.

ROY Over a year ago

I suppose email id will always contain @ so where condition is not necessary.

Ilyes · Accepted Answer · 2019-06-15 09:42:59Z

2

You can use REPLACE() with CHARINDEX() as

CREATE TABLE Strings(
  Str VARCHAR(45)
);

INSERT INTO Strings VALUES
('[email protected]'),  
('[email protected]'),  
('[email protected]');

UPDATE Strings
SET Str = REPLACE(Str, SUBSTRING(Str, CHARINDEX('@',Str), LEN(Str)), '@outlook.com');

SELECT *
FROM Strings;

Here is also another way

UPDATE S
SET S.Str = REPLACE(S.Str, E.V, '@outlook.com')
FROM Strings S JOIN (VALUES('@gmail.com'), ('@hotmail.com'), ('@yahoo.com')) E(V)
ON S.Str LIKE CONCAT('%', E.V, '%');

Demo1

Demo2

edited Jun 15, 2019 at 9:42

answered Jun 15, 2019 at 8:34

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Comments

Vahid Farahmandian · Accepted Answer · 2019-06-15 10:56:51Z

0

If you are using SQL Server 2017, you can use this as a subquery inside your main query:

SELECT FIRST_VALUE(value) OVER (ORDER BY (SELECT NULL)) + '@outlook.com'
FROM STRING_SPLIT('[email protected]', '@');

Read more about FIRST_VALUE: https://learn.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-2017

Read more about STRING_SPLIT: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Update

As @TimBiegeleisen mentioned, Microsoft docs says:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string

To overcome that, we can even rewrite the query as:

SELECT value + '@outlook.com'
FROM
(
    SELECT value,
           CHARINDEX('@', '[email protected]') AS atIndex,
           CHARINDEX(value, '[email protected]') AS partIndex
    FROM STRING_SPLIT('[email protected]', '@')
) AS K
WHERE K.atIndex > K.partIndex;

edited Jun 15, 2019 at 10:56

answered Jun 15, 2019 at 10:40

Vahid Farahmandian

6,6227 gold badges48 silver badges68 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

If you read the documentation for STRING_SPLIT closely, you'll also notice that there are no guarantees about the order of the table. So, FIRST_VALUE may not always work here.

Vahid Farahmandian Over a year ago

@TimBiegeleisen Great notice. I have updated the answer

Suraj Kumar · Accepted Answer · 2019-06-15 09:24:00Z

-1

You can use the following queries replace tbl_name and column_name according to your data:

UPDATE tbl_name SET column_name = replace(column_name, 'gmail.com', 'outlook.com');    
UPDATE tbl_name SET column_name = replace(column_name, 'yahoo.com', 'outlook.com');    
UPDATE tbl_name SET column_name = replace(column_name, 'hotmail.com', 'outlook.com');

edited Jun 15, 2019 at 9:24

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

answered Jun 15, 2019 at 9:13

Tej

134 bronze badges

2 Comments

Thom A Over a year ago

And what about every other domain out there? This won't scale well.

HABO Over a year ago

"[email protected]" becomes what? Well, "gmail.com" is in there so ... .

Collectives™ on Stack Overflow

MSSQL Replace string with another string after a certain character

4 Answers 4

3 Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related