CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringcharindex
ROY
ROY

Reputation: 33

MSSQL Replace string with another string after a certain character

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.

Upvotes: 0

Views: 2153

Answers (4)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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;

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 2

Tej
Tej

Reputation: 13

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

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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

Upvotes: 2

Related Questions