The CONCAT_WS SQL Function in MySQL and MariaDB - Concatenation of strings with separator |
|
| | CONCAT_WS | Syntax: | CONCAT_WS(Separator, Strings ('str1','str2', ...)) | | Return value: | CHAR, BINARY | | Function type: | String function | |
| | The CONCAT_WS() function returns a string resulting from concatenating two or more arguments with a separator.
If either argument contains a non-binary string, the result is a string in the argument's character set.
If arguments contain different character sets, the result is returned in the "larger" character set.
Example: CONCAT_WS('Sep',Latin1, UTF8) = UTF8.
If all arguments contain binary strings, the result is a binary string.
Numeric values in arguments are converted to their equivalent binary string form.
To avoid this, an explicit cast can be used.
Unlike the CONCAT() function, if any of the arguments is NULL, the argument is passed over.
If the value of the separator is NULL, the CONCAT_WS() function returns NULL.
To concatenate strings without a separator, the CONCAT() function can be used. | SQL Examples for the CONCAT_WS function |
| select concat_ws('-SEP-' ,'Test');
select concat_ws('-SEP-' ,'Test', '-Test');
select concat_ws('-SEP-' ,'Test', '-', 123);
select concat_ws('-SEP-' ,'Test', '-', now());
select concat_ws('-SEP-' ,'Test', null, '-Test');
select concat_ws('-SEP-' ,'Test', ifnull(null,''), '-Test');
select concat_ws(null ,'Test', null, '-Test');
|
|
| concat_ws('-SEP-' ,'Test') |
|---|
| varchar(4) BINARY |
|---|
| Test |
|
|
| concat_ws('-SEP-' ,'Test', '-Test') |
|---|
| varchar(14) BINARY |
|---|
| Test-SEP--Test |
|
|
| concat_ws('-SEP-' ,'Test', '-', 123) |
|---|
| varchar(18) BINARY |
|---|
| Test-SEP---SEP-123 |
|
|
| concat_ws('-SEP-' ,'Test', '-', now()) |
|---|
| varchar(34) BINARY |
|---|
| Test-SEP---SEP-2023-03-22 12:24:12 |
|
|
| concat_ws('-SEP-' ,'Test', null, '-Test') |
|---|
| varchar(19) BINARY |
|---|
| Test-SEP--Test |
|
|
| concat_ws('-SEP-' ,'Test', ifnull(null,''), '-Test') |
|---|
| varchar(19) BINARY |
|---|
| Test-SEP--SEP--Test |
|
|
| concat_ws(null ,'Test', null, '-Test') |
|---|
| varchar(9) BINARY |
|---|
| NULL |
|
|
| Examples for the output of character sets |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
| select charset(concat_ws('-' ,'Test', '-Test'));
select charset(concat_ws('-' ,'Test', '-', 123));
select charset(concat_ws('-' ,'Test', '-', now()));
select charset(concat_ws('-' ,convert('Test' using utf8), '-Test'));
select charset(concat_ws('-' ,convert('Test' using latin1), '-', 123));
select charset(concat_ws('-' ,`eng`, '-', 123)) from menu where id=5;
select concat_ws('-' ,convert('Test' using utf8), convert('-Test' using latin1));
select charset(concat_ws('-' , 123, convert('Test' using utf8), convert('-Test' using latin1)));
select charset(concat_ws('-' , 123, convert('Test' using latin1), convert('-Test' using utf8)));
select charset(concat_ws('-' , 123, 'Test', convert('-Test' using latin1)));
|
|
| charset(concat_ws('-' ,'Test', '-Test')) |
|---|
| varchar(64) BINARY |
|---|
| binary |
|
|
| charset(concat_ws('-' ,'Test', '-', 123)) |
|---|
| varchar(64) BINARY |
|---|
| binary |
|
|
| charset(concat_ws('-' ,'Test', '-', now())) |
|---|
| varchar(64) BINARY |
|---|
| binary |
|
|
| charset(concat_ws('-' ,convert('Test' using utf8), '-Test')) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| charset(concat_ws('-' ,convert('Test' using latin1), '-', 123)) |
|---|
| varchar(64) BINARY |
|---|
| latin1 |
|
|
| charset(concat_ws('-' ,`eng`, '-', 123)) |
|---|
| varchar(64) BINARY |
|---|
| latin1 |
|
|
| concat_ws('-' ,convert('Test' using utf8), convert('-Test' using latin1)) |
|---|
| varchar(10) BINARY |
|---|
| Test--Test |
|
|
| charset(concat_ws('-' , 123, convert('Test' using utf8), convert('-Test' using latin1))) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| charset(concat_ws('-' , 123, convert('Test' using latin1), convert('-Test' using utf8))) |
|---|
| varchar(64) BINARY |
|---|
| utf8 |
|
|
| charset(concat_ws('-' , 123, 'Test', convert('-Test' using latin1))) |
|---|
| varchar(64) BINARY |
|---|
| latin1 |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the CONCAT_WS() function in MySQL and MariaDB databases | The CONCAT_WS() function is used to concatenate multiple strings with a specified delimiter. It stands for Concatenate With Separator. The function takes a delimiter string followed by two or more strings and returns a single string that is the result of concatenating the input strings with the specified delimiters. Any number of strings can be concatenated by providing them as separate arguments to the function.
The CONCAT_WS() function in MySQL and MariaDB is useful when concatenating strings with a specific delimiter should, for example, when creating comma-separated lists or when creating formatted output. | | Further MySQL und MariaDB SQL String functions | | CHAR | CHAR(Character code, [, Charset]) | | More about CHAR Function |
| | CHR | CHR(Character code) | | More about CHR Function |
| | CONCAT | CONCAT(Strings ('str1','str2', ...)) | | More about CONCAT Function |
| | ELT | ELT(Index, Strings ('str1','str2', ...)) | | More about ELT Function |
| | EXPORT_SET | EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits]) | | More about EXPORT_SET Function |
| | EXTRACTVALUE | EXTRACTVALUE(XML, XPath) |
| | FIELD | FIELD(Search string, Strings ('str1','str2', ...)) | | More about FIELD Function |
| | FORMAT | FORMAT(Number, Decimals [, Region]) | | More about FORMAT Function |
| | INSERT | INSERT(String, Position, Length, String to insert) | | More about INSERT Function |
| | LCASE | LCASE(String) | | More about LCASE Function |
| | LEFT | LEFT(String, Length) | | More about LEFT Function |
| | LOAD_FILE | LOAD_FILE(File name) |
| | LOWER | LOWER(String) | | More about LOWER Function |
| | LPAD | LPAD(String, Length, String to append) | | More about LPAD Function |
| | LPAD_ORACLE | LPAD_ORACLE(String, Length, String to append) | | More about LPAD_ORACLE Function |
| | LTRIM | LTRIM(String) | | More about LTRIM Function |
| | MAKE_SET | MAKE_SET(Bits, Strings ('str1','str2', ...)) | | More about MAKE_SET Function |
| | MID | MID(String, Position [, Length]) | | More about MID Function |
| | QUOTE | QUOTE(String) | | More about QUOTE Function |
| | REPEAT | REPEAT(String, Number) | | More about REPEAT Function |
| | REPLACE | REPLACE(String, Search, Replace) | | More about REPLACE Function |
| | REVERSE | REVERSE(String) | | More about REVERSE Function |
| | RIGHT | RIGHT(String, Length) | | More about RIGHT Function |
| | RPAD | RPAD(String, Length, String to append) | | More about RPAD Function |
| | RPAD_ORACLE | RPAD_ORACLE(String, Length, String to append) | | More about RPAD_ORACLE Function |
| | RTRIM | RTRIM(String) | | More about RTRIM Function |
| | SOUNDEX | SOUNDEX(String) | | More about SOUNDEX Function |
| | SPACE | SPACE(Number) | | More about SPACE Function |
| | SUBSTR | SUBSTR(String, Position, Length) | | More about SUBSTR Function |
| | SUBSTRING | SUBSTRING(String, Position, Length) | | More about SUBSTRING Function |
| | SUBSTRING_INDEX | SUBSTRING_INDEX(String, Delimiter, Number) | | More about SUBSTRING_INDEX Function |
| | TRIM | TRIM([Type] [, Delete character], , String) | | More about TRIM Function |
| | TRIM_ORACLE | TRIM_ORACLE([Type] [, Delete character], , String) | | More about TRIM_ORACLE Function |
| | UCASE | UCASE(String) | | More about UCASE Function |
| | UPPER | UPPER(String) | | More about UPPER Function |
| | WEIGHT_STRING | WEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting]) | | More about WEIGHT_STRING Function |
| | ASCII | ASCII(String) | | More about ASCII Function |
| | BIT_LENGTH | BIT_LENGTH(String) | | More about BIT_LENGTH Function |
| | CHAR_LENGTH | CHAR_LENGTH(String) | | More about CHAR_LENGTH Function |
| | CHARACTER_LENGTH | CHARACTER_LENGTH(String) | | More about CHARACTER_LENGTH Function |
| | FIND_IN_SET | FIND_IN_SET(Search string, Set of values) | | More about FIND_IN_SET Function |
| | INSTR | INSTR(String, Search string) | | More about INSTR Function |
| | LENGTH | LENGTH(String) | | More about LENGTH Function |
| | LENGTHB | LENGTHB(String) | | More about LENGTHB Function |
| | LOCATE | LOCATE(Search string, String [, Position]) | | More about LOCATE Function |
| | OCTET_LENGTH | OCTET_LENGTH(String) | | More about OCTET_LENGTH Function |
| | ORD | ORD(String) | | More about ORD Function |
| | POSITION | POSITION(Search string IN String) | | More about POSITION Function |
|
|
| | | More information about the CONCAT_WS SQL function: and and |
|
|
|
|
