SQL, replace part of a string with email

sql replace part of string
sql replace string in column
sql replace regex
replace column value in sql
sql replace multiple characters
sql replace multiple values
sql replace wildcard
sql update substring

In my DB I have a column user_email with values:

aaa@test.com
bbb@test.com
ccc@test.com

I would only like to change part of email address that comes after @, so that the resulting column would have values:

aaa@other.net
bbb@other.net
ccc@other.net

How could I achieve that?

I've found following solution that seems to do the trick:

UPDATE table_name SET user_email = REPLACE(user_email, '@test.com', '@other.net');

SQL REPLACE Function: Search and Replace String in Database, SQL REPLACE Function: Search and Replace String in Database UPDATE employees SET email = REPLACE(email, 'sqltutorial.org', 'acme.com') WHERE  REPLACE ( string, old_substring, new_substring); The REPLACE function will search for all occurrences of the old_substring and replace it with the new_string. The following statement replaces all the occurrences of bar with foo so the result is bar bar bar. SELECT REPLACE ( 'foo foo bar', 'foo', 'bar' ); -- bar bar bar.

use replace function

demo

select replace(name,substring(name,position('@' in name),length(name)-position('@' in name)+1),'@other.net')

select replace('aaa@test.com',substring('aaa@test.com',position('@' in 'aaa@test.com'),
length('aaa@test.com')-position('@' in 'aaa@test.com')+1),'@other.net')

output:

val             n
aaa@test.com    aaa@other.net

SQL Server REPLACE() Function, The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the  Definition and Usage. The REPLACE () function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF () function.

use SUBSTRING_INDEX and concat fnction

select concat(SUBSTRING_INDEX("aaa@test.com", "@", 1),'@other.net')

output aaa@other.net

so for your column user_email it would be

select concat(SUBSTRING_INDEX(user_email, "@", 1),'@other.net')

Replace email domain with example.com in SQL (Example), DECLARE @var varchar(50) SET @var = 'abc@exe.com'; SELECT @var as '​Original', SUBSTRING(@var, CHARINDEX('@', @var), LEN(@var) -  Changing part of a string in SQL Server using REPLACE – 3/11, Victoria Yudin | Partner Compete - March 11, 2013. […] Continue reading on Source Blog […] Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) You are commenting using your WordPress.com account. ( Log Out / Change )

select concat
        (substring
          ('bbb@test.com',1,char_length
            ('bbb@test.com')-
            char_length
              (substring_index
                ('bbb@test.com','.',-1))),'net') x;

| x            |
| ------------ |
| bbb@test.net |

View on DB Fiddle

PostgreSQL REPLACE, Sometimes, you want to search and replace a string in a column with a new one Now, suppose you want to update the email column to replace the domain  To remove the part of string before the specific character, you use these transact-sql string functions as follow: SELECT REPLACE(SUBSTRING(string_expression, CHARINDEX(expression_to_find, string_expression), LEN(string_expression)), string_pattern, string_replacement) Demo

You might use replace, substr and instr together as :

SELECT replace( 'aaa@test.com',
         substr('aaa@test.com',instr('aaa@test.com','@'),length('aaa@test.com'))
                              ,'@other.net') as result_str;

 result_str
-------------
aaa@other.net

or from your table(tab) with a column called as email :

select replace(email,substr(email,instr(email,'@'),length(email)),'@other.net') result_str
  from tab;

result_str
-------------
aaa@other.net
bbb@other.net
ccc@other.net

Rextester Demo

Overview of the SQL REPLACE function, Pattern: The substring to evaluate and provides a reference position to the replacement field. REPLACEment: REPLACEs the specified string or  APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Replaces all occurrences of a specified string value with another string value. Transact-SQL Syntax Conventions. Syntax REPLACE ( string_expression , string_pattern , string_replacement ) Arguments. string_expression Is the string expression to be searched.

MySQL REPLACE String Function: Find & Replace Text, MySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table by a new string. The syntax of the  Using SUBSTRING with a character string The following example shows how to return only a part of a character string. From the sys.databases table, this query returns the system database names in the first column, the first letter of the database in the second column, and the third and fourth characters in the final column.

REPLACE (Transact-SQL), Transact-SQL reference for the REPLACE function, which replaces all occurrences of a Twitter · LinkedIn · Facebook · Email Is the substring to be found. string_pattern can be of a character or binary data type. Is the replacement string. string_replacement can be of a character or binary data type. Sounds very much like a homework question. What have you tried? One approach would be to use a splitter function to split that part the string into its individual characters, do the replace, then

PATINDEX (Transact-SQL), pattern is an expression of the character string data type category. pattern is limited to 8000 characters. Note. While traditional regular  The REPLACE () function is often used to correct data in a table. For example, replacing the outdated link with the new one. The following is the syntax: column_name = REPLACE(column_name, 'old_string','new_string') For example, to change the city code of the phone numbers from 916 to 917, you use the following statement: sales.customers.

Comments
  • That will only work for domain @test.com in case you have @test1.com or any other you wont get desired output.
  • Got it. All my users in DB have email from one domain and I just needed to replace that with new domain.
  • You have your solution then :) Happy coding
  • The op wants to change everything after the LAST dot