String functions such as replace, concat, and cast in mysql

Replace, concat, and cast character string functions in mysql need to export a personal information of job-seeking users. This information requires three-table joint query requirements: birthdate format: 1990.01.02 Database: Gender display: men and women databases are int type: 01 (corresponding to men and women respectively) solution: three tables join query 1SELECT * FROM (Table

Replace, concat, and cast character string functions in mysql need to export a personal information of job-seeking users. This information requires three-table joint query requirements: birthdate format: 1990.01.02 Database: Gender display: male and female databases are int type: 0 1 (corresponding to men and women respectively) solution: three tables join query 1 SELECT * FROM (Table

Replace, concat, and cast string functions in mysql

You need to export the personal information of the job-seeking user.

1. Three-table join query is required
2. Required birthdate format: 1990.01.02 database:
3. Gender display: the male and female databases are int type: 0 1 (corresponding to men and women respectively)

Solution:

1. Three-table join query

1	SELECT* FROM(Table 1INNERJOIN Table 2ONTable 1. Field number = TABLE 2. Field number)

2	INNERJOIN Table 3ONTable 1. Field number = TABLE 3. Field number

2. Time formatting

DATE_FORMAT(date,format)

DateThe parameter is a valid date.FormatSpecifies the output format of the date/time.

See: http://www.w3school.com.cn/ SQL /func_date_format.asp

3. Gender display is complicated.

Replace the replace function in mysql

Replace in mysql

For example, replace abc of f1 in Table tb1 with def.

REPLACE (f1, 'abc', 'def ');

Ps: in this case, you can use mysql to batch modify

For example, modify mysql statements in batches.

UPDATE article SETcontent = replace (content, 'php10086', 'php Huaibei ') where id <5000;

However, replace applies to replacement of the same type. If it is not a field of the same type, the field type needs to be converted.

Cast in mysql

The concat function is often used to convert Int to varchar. For example, the concat (8, '0') character string '80' is obtained'

Convert varchar to Int using cast (a as signed) a AS a varchar string, such as CAST (c. sex as char)

Available types

Binary, with a BINARY Prefix: binary

Character type, with parameters: CHAR ()

DATE: DATE

TIME: TIME

DATETIME type

Floating Point: DECIMAL

Integer: SIGNED

UNSIGNED integer

Concat in mysql

Concat

Concat functions in MySQL

Usage:

CONCAT (str1, str2 ,...)

Returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL.

Note:

If all parameters are non-binary strings, the result is a non-binary string.

If the independent variable contains any binary string, the result is a binary string.

A numeric parameter is converted to an equivalent binary string format. To avoid this, you can use an explicit cast type, for example:

Select concat (CAST (int_col as char), char_col)

The concat function of MySQL can connect one or more strings, such

Mysql> select concat ('10 ′);

+ ----- +

| Concat ('10') |

+ ----- +

| 10 |

+ ----- +

1 row in set (0.00 sec)

Mysql> select concat ('11', '22', '33 ′);

+ -------- +

| Concat ('11', '22', '33') |

+ -------- +

| 1, 112233 |

+ -------- +

1 row in set (0.00 sec)

When the concat function of MySQL connects to a string, if one of them is NULL, NULL is returned.

Mysql> select concat ('11', '22', null );

+ -------- +

| Concat ('11', '22', null) |

+ -------- +

| NULL |

+ -------- +

1 row in set (0.00 sec)

Complete SQL:

1	selectc.nameas 'Name',REPLACE(REPLACE(CAST(c.sexASCHAR),'0',Male'),'1',Female')as'Gender',c.nationas'Mine',DATE_FORMAT(c.birthday,'%Y.%m.%d')as'Birthday',

2	c.provinceas'Origin source',c.politicsas'Political face',c.schoolas'Graduation school',c.majoras'Professional',

3	c.degreeas'Diploma',c.phoneas'Telephony',c.majoras'Professional',p.titleas'Post'

4	from(map asm leftjoin c_resume asc onc.uid=m.contentid)

5	leftjoin program asp onm.pid = p.id wherem.intro=1 limit 60,90

Result: Gender: male and female (correct)

I used replace twice to replace 01 with male.

Previously, I always wanted to use concat to merge fields but it was not successful.

Select c. name as 'name', concat (REPLACE (CAST (c. sex as char), '0', 'male'), REPLACE (CAST (c. sex as char), '1', 'female ') as 'gender', c. nation as 'mine', DATE_FORMAT (c. birthday, '% Y. % m. % D') as 'birthdate ',

C. province as 'origin source', c. politics as 'political face', c. school as 'Graduation school', c. major as 'professional ',

C. degree as 'diploma ', c. phone as 'telephony', c. major as 'professional', p. title as 'post'
From (map as m left join c_resume as c on c. uid = m. contentid)

Left join program as p on m. pid = p. id where m. intro = 1 limit 60, 90

Result: Gender: male 0, female 1 (unsuccessful)

Mysql string functions

Article with a mysql string function: http://www.phpv.net/html/1443.html

For operations on the string position, the first position is marked as 1.

ASCII(str)

Returns a string.strASCII code value of the leftmost character.If strIs a null string and returns 0. If strYes NULL, Return NULL.

mysql> select ASCII('2');        -> 50mysql> select ASCII(2);        -> 50mysql> select ASCII('dx');        -> 100

For more information, see the ORD () function.

ORD(str)

If the leftmost character of a string 'str' is a multi-byte character ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]Returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns and ASCII()The same value returned by the function.

mysql> select ORD('2');        -> 50 

CONV(N,from_base,to_base)

Convert numbers between different digit bases. Return number NString number, from from_baseBase transform to_baseBase, if any parameter is NULL, Return NULL. Parameters NIt is interpreted as an integer, but can be specified as an integer or a string. Minimum base is 2And the largest base is 36. If to_baseIs a negative number, NIt is considered as a signed number. Otherwise, NIt is treated as an unsigned number. CONVWork with 64-point precision.

mysql> select CONV("a",16,2);        -> '1010'mysql> select CONV("6E",18,8);        -> '172'mysql> select CONV(-17,10,-18);        -> '-H'mysql> select CONV(10+"10"+'10'+0xa,10,10);        -> '40' 

BIN(N)

Returns the binary value. NIn NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,2). If NYes NULL, Return NULL.

mysql> select BIN(12);        -> '1100'

OCT(N)

Returns the octal value. NRepresents a string in NIs a long integer, which is equivalent CONV(N,10,8). If NIs NULL, return NULL.

mysql> select OCT(12);        -> '14' 

HEX(N)

Returns the hexadecimal value. NRepresents a string. NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,16). If NYes NULL, Return NULL.

mysql> select HEX(255);        -> 'FF' 

CHAR(N,...)

CHAR()Interpreted as an integer and returns a string consisting of ASCII code characters of these integers. NULLThe value is skipped.

mysql> select CHAR(77,121,83,81,'76');        -> 'MySQL'mysql> select CHAR(77,77.3,'77.3');        -> 'MMM' 

CONCAT(str1,str2,...)

Returns a string from the parameter link.. If any parameter is NULL, Return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.

mysql> select CONCAT('My', 'S', 'QL');        -> 'MySQL'mysql> select CONCAT('My', NULL, 'QL');        -> NULLmysql> select CONCAT(14.3);        -> '14.3'

LENGTH(str)

OCTET_LENGTH(str)

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

Returns a string. str.

mysql> select LENGTH('text');        -> 4mysql> select OCTET_LENGTH('text');        -> 4

Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.

LOCATE(substr,str)

POSITION(substr IN str)

Returns a substring. substrIn the string strThe first position that appears, if substrNot in strAnd returns 0.

mysql> select LOCATE('bar', 'foobarbar');        -> 4mysql> select LOCATE('xbar', 'foobar');        -> 0

This function is multi-byte reliable.

LOCATE(substr,str,pos)

Returns a substring. substrIn the string strThe first position that appears, starting from the position posStart. If substrNot in strAnd returns 0.

mysql> select LOCATE('bar', 'foobarbar',5);        -> 7

This function is multi-byte reliable.

INSTR(str,substr)

Returns a substring. substrIn the string strThe first position in. This is in the form of two parameters LOCATE()Same, except that the parameters are reversed.

mysql> select INSTR('foobarbar', 'bar');        -> 4mysql> select INSTR('xbar', 'foobar');        -> 0

This function is multi-byte reliable.

LPAD(str,len,padstr)

Returns a string. str, Use a string on the left padstrFill strYes lenCharacters long.

mysql> select LPAD('hi',4,'??');        -> '??hi' 

RPAD(str,len,padstr)

Returns a string. str, String on the right padstrFill strYes lenCharacters long.

mysql> select RPAD('hi',5,'?');        -> 'hi???'

LEFT(str,len)

Returns a string. strThe leftmost area lenCharacters.

mysql> select LEFT('foobarbar', 5);        -> 'fooba'

This function is multi-byte reliable.

RIGHT(str,len)

Returns a string. strThe rightmost lenCharacters 。

mysql> select RIGHT('foobarbar', 4);        -> 'rbar'

This function is multi-byte reliable.

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

From string strReturns lenSubstring, starting from position posStart. Use FROMThe variant form is the ANSI SQL92 syntax.

mysql> select SUBSTRING('Quadratically',5,6);        -> 'ratica'

This function is multi-byte reliable.

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

From string strStart position posReturns a substring.

mysql> select SUBSTRING('Quadratically',5);        -> 'ratically'mysql> select SUBSTRING('foobarbar' FROM 4);        -> 'barbar'

This function is multi-byte reliable.

SUBSTRING_INDEX(str,delim,count)

Returns the string strThe countAppears OfDelimiter delimSubstring. If countIs a positive number, returns all characters from the last separator to the left (number from the left. If countIs a negative number, returns all characters (from the right) from the last separator to the right ).

mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);        -> 'www.mysql'mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);        -> 'mysql.com'

This function is reliable for multiple bytes.

LTRIM(str)

Returns the string with leading space characters deleted. str.

mysql> select LTRIM('  barbar');        -> 'barbar'

RTRIM(str)

Returns the string with spaces after the string is deleted. str.

mysql> select RTRIM('barbar   ');        -> 'barbar'

This function is reliable for multiple bytes.

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns a string. str, All of them remstrThe prefix or suffix is deleted. If there is no Modifier BOTH, LEADINGOr TRAILINGGiven, BOTHIs assumed. If remstrNot specified. spaces are deleted.

mysql> select TRIM('  bar   ');        -> 'bar'mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');        -> 'barxxx'mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');        -> 'bar'mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');        -> 'barx'

This function is reliable for multiple bytes.

SOUNDEX(str)

Return str. The two strings that sound "roughly the same" should have the same homophone string. The length of a "standard" homophone string is 4 characters, SOUNDEX()The function returns a string of any length. You can use SUBSTRING()Get a standard homophone. All non-alphanumeric characters are ignored in a given string. All international letters except the A-Z are treated as vowels.

mysql> select SOUNDEX('Hello');        -> 'H400'mysql> select SOUNDEX('Quadratically');        -> 'Q36324' 

SPACE(N)

Returns NA string consisting of space characters.

mysql> select SPACE(6);        -> '      ' 

REPLACE(str,from_str,to_str)

Returns a string. str, Its string from_strAll appear by string to_str.

mysql> select REPLACE('www.mysql.com', 'w', 'Ww');        -> 'WwWwWw.mysql.com'

This function is reliable for multiple bytes.

REPEAT(str,count)

Returns a duplicate countTimes string strA string. If count <= 0Returns an empty string. If strOr countYes NULL, Return NULL.

mysql> select REPEAT('MySQL', 3);        -> 'MySQLMySQLMySQL' 

REVERSE(str)

Returns a string in the reversed character order. str.

mysql> select REVERSE('abc');        -> 'cba'

This function is reliable for multiple bytes.

INSERT(str,pos,len,newstr)

Returns a string. str, In the location posAnd lenCharacter long substring by string newstr.

mysql> select INSERT('Quadratic', 3, 4, 'What');        -> 'QuWhattic'

This function is reliable for multiple bytes.

ELT(N,str1,str2,str3,...)

If N= 1, Return str1, If N= 2, Return str2And so on. If NLess 1Or greater than the number of parameters, return NULL. ELT()Yes FIELD()Inverse Operation.

mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');        -> 'ej'mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');        -> 'foo'

FIELD(str,str1,str2,str3,...)

Return strIn str1, str2, str3, ...Index of the list. If strNo. 0. FIELD()Yes ELT()Inverse Operation.

mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');        -> 2mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');        -> 0

FIND_IN_SET(str,strlist)

If the string strIn NTable composed of substrings strlistReturns 1To N. A string table is ","A string composed of substrings. If the first parameter is a constant string and the second parameter is of SET, FIND_IN_SET()Bitwise operations are used when functions are optimized! If strNot in strlistOr if strlistIs a null string and returns 0. If any parameter is NULL, Return NULL. If the first parameter contains ","This function will not work properly.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');        -> 2 

MAKE_SET(bits,str1,str2,...)

Returns a set (including ","A string consisting of substrings separated by characters. bitsThe string in the set. str1Corresponding to bit 0, str2Corresponding bit 1, and so on. In str1, str2, ...In NULLThe string is not added to the result.

mysql> SELECT MAKE_SET(1,'a','b','c');        -> 'a'mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');        -> 'hello,world'mysql> SELECT MAKE_SET(0,'a','b','c');        -> ''

EXPORT_SET(bits,on,off,[separator,[number_of_bits]])

Returns a string. Here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, you get a "off" string. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used.

mysql> select EXPORT_SET(5,'Y','N',',',4)        -> Y,N,Y,N 

LCASE(str)

LOWER(str)

Returns a string. str, Converts all characters to lowercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.

mysql> select LCASE('QUADRATICALLY');        -> 'quadratically' 

UCASE(str)

UPPER(str)

Returns a string. str, According to the current character set ing (default is ISO-8859-1 Latin1) to change all the characters into uppercase. This function is reliable for multiple bytes.

mysql> select UCASE('Hej');        -> 'HEJ'

This function is reliable for multiple bytes.

LOAD_FILE(file_name)

Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have FilePermission. All file content must be readable and smaller max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.

mysql> UPDATE table_name           SET blob_column=LOAD_FILE("/tmp/picture")           WHERE id=1;

MySQLIf necessary, the number is automatically converted into a string, and the reverse is also true:

mysql> SELECT 1+"1";        -> 2mysql> SELECT CONCAT(2,' test');        -> '2 test'

If you want to explicitly convert a number to a string, pass it as a parameterCONCAT().

If a string function provides a binary string as a parameter, the result string is also a binary string. The number converted to a string is treated as a binary string. This only affects the comparison.

Replace, concat, cast, and mysql string functions in mysql

Cast, concat, replace in mysql, mysql string Function
String functions such as replace, concat, and cast in mysql
Contact Author:PHP Huaibei, communication QQ group: 153856687
PHP10086
About this site:It is said that every programmer should dig his own well: http://www.php10086.com.
Subscribe to updates:Welcome to subscribe to our content update through RSS