©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The REPLACE SQL Function in MySQL and MariaDB - Search and replace

REPLACESyntax:REPLACE(String, Search, Replace)
Return value:CHAR
Function type:String function
Function Description

The REPLACE() function returns the "String" with all occurrences of the string "Search" being replaced with the string "Replace".

REPLACE() is case sensitive.

If the "String" and "Replace" arguments contain different charsets, the result is returned in the "larger" character set.

If the "Search" argument is empty, no replacement is made.

If either argument is NULL, the REPLACE() function returns NULL.

SQL Examples for the REPLACE function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

select replace('Test''s''x');
select replace('Test''S''X');
select replace('Test''es''x');

select replace('Test''''X');
select replace('Test''s''');

select replace(null'es''x');
select replace('Test'null'x');
select replace('Test''es'null);

replace('Test', 's', 'x')
varchar(4) BINARY
Text
replace('Test', 'S', 'X')
varchar(4) BINARY
Test
replace('Test', 'es', 'x')
varchar(4) BINARY
Txt
replace('Test', '', 'X')
varchar(4) BINARY
Test
replace('Test', 's', '')
varchar(4) BINARY
Tet
replace(null, 'es', 'x')
varchar(0) BINARY
NULL
replace('Test', null, 'x')
varchar(4) BINARY
NULL
replace('Test', 'es', null)
varchar(4) BINARY
NULL

Examples for the output of character sets with REPLACE()

 1
 2
 3
 4
 5
 6
 7

select charset(replace('Test''s''x'));
select charset(replace(convert('Test' using utf8), 's''x'));
select charset(replace('Test''s'convert('x' using utf8)));
select charset(replace(convert('Test' using latin1), 's''x'));
select charset(replace(convert('Test' using latin1), 's'convert('x' using utf8)));

charset(replace('Test', 's', 'x'))
varchar(64) BINARY
binary
charset(replace(convert('Test' using utf8), 's', 'x'))
varchar(64) BINARY
utf8
charset(replace('Test', 's', convert('x' using utf8)))
varchar(64) BINARY
utf8
charset(replace(convert('Test' using latin1), 's', 'x'))
varchar(64) BINARY
latin1
charset(replace(convert('Test' using latin1), 's', convert('x' using utf8)))
varchar(64) BINARY
utf8
The examples were created with the MyWAY SQL manager: Download

How to use the REPLACE() function in MySQL and MariaDB databases

In MySQL and MariaDB, the REPLACE() function is used to return all occurrences of a substring within a string to be replaced with a specified replacement string. The function is useful when you need to find and replace specific substrings within a string. The function can be used for tasks such as manipulating data, performing string transformations, or normalizing data values.

Further MySQL und MariaDB SQL String functions

CHARCHAR(Character code, [, Charset])
More about CHAR Function

CHRCHR(Character code)
More about CHR Function

CONCATCONCAT(Strings ('str1','str2', ...))
More about CONCAT Function

CONCAT_WSCONCAT_WS(Separator, Strings ('str1','str2', ...))
More about CONCAT_WS Function

ELTELT(Index, Strings ('str1','str2', ...))
More about ELT Function

EXPORT_SETEXPORT_SET(Bits, On, Off [, Separator] [, Number of bits])
More about EXPORT_SET Function

EXTRACTVALUEEXTRACTVALUE(XML, XPath)

FIELDFIELD(Search string, Strings ('str1','str2', ...))
More about FIELD Function

FORMATFORMAT(Number, Decimals [, Region])
More about FORMAT Function

INSERTINSERT(String, Position, Length, String to insert)
More about INSERT Function

LCASELCASE(String)
More about LCASE Function

LEFTLEFT(String, Length)
More about LEFT Function

LOAD_FILELOAD_FILE(File name)

LOWERLOWER(String)
More about LOWER Function

LPADLPAD(String, Length, String to append)
More about LPAD Function

LPAD_ORACLELPAD_ORACLE(String, Length, String to append)
More about LPAD_ORACLE Function

LTRIMLTRIM(String)
More about LTRIM Function

MAKE_SETMAKE_SET(Bits, Strings ('str1','str2', ...))
More about MAKE_SET Function

MIDMID(String, Position [, Length])
More about MID Function

QUOTEQUOTE(String)
More about QUOTE Function

REPEATREPEAT(String, Number)
More about REPEAT Function

REVERSEREVERSE(String)
More about REVERSE Function

RIGHTRIGHT(String, Length)
More about RIGHT Function

RPADRPAD(String, Length, String to append)
More about RPAD Function

RPAD_ORACLERPAD_ORACLE(String, Length, String to append)
More about RPAD_ORACLE Function

RTRIMRTRIM(String)
More about RTRIM Function

SOUNDEXSOUNDEX(String)
More about SOUNDEX Function

SPACESPACE(Number)
More about SPACE Function

SUBSTRSUBSTR(String, Position, Length)
More about SUBSTR Function

SUBSTRINGSUBSTRING(String, Position, Length)
More about SUBSTRING Function

SUBSTRING_INDEXSUBSTRING_INDEX(String, Delimiter, Number)
More about SUBSTRING_INDEX Function

TRIMTRIM([Type] [, Delete character], , String)
More about TRIM Function

TRIM_ORACLETRIM_ORACLE([Type] [, Delete character], , String)
More about TRIM_ORACLE Function

UCASEUCASE(String)
More about UCASE Function

UPPERUPPER(String)
More about UPPER Function

WEIGHT_STRINGWEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting])
More about WEIGHT_STRING Function

ASCIIASCII(String)
More about ASCII Function

BIT_LENGTHBIT_LENGTH(String)
More about BIT_LENGTH Function

CHAR_LENGTHCHAR_LENGTH(String)
More about CHAR_LENGTH Function

CHARACTER_LENGTHCHARACTER_LENGTH(String)
More about CHARACTER_LENGTH Function

FIND_IN_SETFIND_IN_SET(Search string, Set of values)
More about FIND_IN_SET Function

INSTRINSTR(String, Search string)
More about INSTR Function

LENGTHLENGTH(String)
More about LENGTH Function

LENGTHBLENGTHB(String)
More about LENGTHB Function

LOCATELOCATE(Search string, String [, Position])
More about LOCATE Function

OCTET_LENGTHOCTET_LENGTH(String)
More about OCTET_LENGTH Function

ORDORD(String)
More about ORD Function

POSITIONPOSITION(Search string IN String)
More about POSITION Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the REPLACE SQL function: mysql.com and mariadb.com and w3schools.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2023 Heino Cunze-Fischer