mysql string operations

2011-06-22  来源:本站原创  分类:Database  人气:121 

mysql-left, right, substr, instr string interception, the interception of decimal float

2007-07-29 14:10

mysql-left, right, substr, instr string interception, the interception of decimal float

2007-04-22 17:31

//------------------------------------------------ -----------------

select avg (stu_oder_percent) from tb_sch_collect

Results:

avg (stu_oder_percent):

60.60962

//------------------------------------------------ -----------------

select left (avg (stu_oder_percent), 4) from tb_sch_collect

Results:

left (avg (stu_oder_percent), 4):

60.6

//------------------------------------------------ -----------------

select right (avg (stu_oder_percent), 7) from tb_sch_collect

Results:

right (avg (stu_oder_percent), 7)

0.60962

//------------------------------------------------ -----------------

instr (avg (stu_oder_percent ),'.')+ 1):

The location of the decimal point after the one that is accurate to one decimal place

select substr (avg (stu_oder_percent), 1, instr (avg (stu_oder_percent ),'.')+ 1) from tb_sch_collect

Results:

60.6

//------------------------------------------------ -------------------------------------------------- ------------------------

MySql string functions

ASCII(str)
Returns a string str leftmost character ASCII code value. If str is an empty string, return 0. If str is NULL, return NULL.
mysql> select ASCII('2');
            -> 50
mysql> select ASCII(2);
            -> 50
mysql> select ASCII('dx');
            -> 100

See also the ORD () function.

ORD(str)
If the left-most characters in the string str is a multi-byte character, the character returned by the format ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...] to return the ASCII code value of multi-byte character code. If the leftmost character is not a multi-byte characters. Return ASCII() function returns the same value.
mysql> select ORD('2');
            -> 50
CONV(N,from_base,to_base)
In the transformation between different digital base number. Returns N string figures, the base from from_base transform to_base base, if any argument is NULL, return NULL. Parameters N interpreted as an integer, but can be specified as an integer or a string. Minimum and the maximum base is 2 base is 36. If to_base is a negative number, N is considered to be a signed, otherwise, N is treated as unsigned. CONV work 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)
Binary value N return a string representation, in this N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,2). If N is NULL, return NULL.
mysql> select BIN(12);
            -> '1100'
OCT(N)
Returns the octal value N a string that, in this N is a long integer number, which is equivalent to CONV(N,10,8). If N is NULL, return NULL.
mysql> select OCT(12);
            -> '14'
HEX(N)
Returns a hexadecimal string representation of the value of N in this N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,16). If N is NULL, return NULL.
mysql> select HEX(255);
            -> 'FF'
CHAR(N,...)
CHAR() interpret the arguments as integers and returns the integer ASCII codes of these characters in a string. NULL value are skipped.
mysql> select CHAR(77,121,83,81,'76');
            -> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
            -> 'MMM'
CONCAT(str1,str2,...)
Return link from the argument string. If any parameter is NULL, return NULL. Can have more than 2 parameters. A numeric argument is converted to equivalent string.
mysql> select CONCAT('My', 'S', 'QL');
            -> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
            -> NULL
mysql> select CONCAT(14.3);
            -> '14.3'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Returns the length of the string str.
mysql> select LENGTH('text');
            -> 4
mysql> select OCTET_LENGTH('text');
            -> 4

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

LOCATE(substr,str)
POSITION(substr IN str)
Return substring in a string str substr first occurrence, if not in str substr inside back 0.
mysql> select LOCATE('bar', 'foobarbar');
            -> 4
mysql> select LOCATE('xbar', 'foobar');
            -> 0
The function is multibyte reliable .
LOCATE(substr,str,pos)
Return substring in a string str substr first occurrence, starting from the position pos. If substr not str inside back 0.
mysql> select LOCATE('bar', 'foobarbar',5);
            -> 7

This function is multi-byte reliable.

INSTR(str,substr)
Return substring substr the first in a string str appear. This is a two-argument form of LOCATE() the same, except parameters are reversed.
mysql> select INSTR('foobarbar', 'bar');
            -> 4
mysql> select INSTR('xbar', 'foobar');
            -> 0

This function is multi-byte reliable.

LPAD(str,len,padstr)
Returns a string str, left to fill until str string padstr is len characters long.
mysql> select LPAD('hi',4,'??');
            -> '??hi'
RPAD(str,len,padstr)
Returns a string str, the right to fill up with string padstr str is len characters long.
mysql> select RPAD('hi',5,'?');
            -> 'hi???'
LEFT(str,len)
Returns a string str leftmost len characters.
mysql> select LEFT('foobarbar', 5);
            -> 'fooba'

This function is multi-byte reliable.

RIGHT(str,len)
Returns the rightmost string str len characters .
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)
Return from a string str len characters of the string, starting from the position pos. Use FROM variant form of ANSI SQL92 syntax.
mysql> select SUBSTRING('Quadratically',5,6);
            -> 'ratica'

This function is multi-byte reliable.

SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
From the beginning of the string str pos returns a substring.
mysql> select SUBSTRING('Quadratically',5);
            -> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

相关文章
  • mysql string operations 2011-06-22

    mysql-left, right, substr, instr string interception, the interception of decimal float 2007-07-29 14:10 mysql-left, right, substr, instr string interception, the interception of decimal float 2007-04-22 17:31 //--------------------------------------

  • linux mysql related operations (transfer) 2010-08-03

    linux mysql installation description: linux server RedHat 5.0.server. MySQL version: Download link: http://dev.mysql.com/downloads/mysql/5.1.html MySQL server: MySQL-server-community-5.1.38-0.rhel5.i386.rpm MySQL client: MySQL-client-community-5.1.38

  • mysql string functions: string interception 2010-08-27

    MySQL string functions: string interception MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is

  • MySQL Multi-table operations and batch details 2010-10-11

    MySQL Multi-table operations and batch details Source: SAN OF: SAN 2007-09-23 Source: pcdog.com dos mysql database backup Multi-table operations In a database, there may be multiple tables, which are interrelated. We continue to use the previous exam

  • linux shell string operations (length, find, replace) Detailed 2011-02-16

    Shell when doing a batch process, often related to the string-related operations. There are a lot of command statements, such as: awk, sed can do various string operations. In fact, a series of shell built-in operator notation, you can achieve a simi

  • [Transfer] shell string operations (length, find, replace) Detailed 2011-08-06

    Originally posted address: http://apps.hi.baidu.com/share/detail/23262717 Shell when doing a batch process, often related to the string-related operations. There are a lot of command statements, such as: awk, sed can do various string operations. In

  • MySQL string functions of the string intercept 2011-08-06

    MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is very powerful and flexible. 1. String interc

  • MySQL string functions: String intercept 2011-08-06

    MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is very powerful and flexible. 1 String interce

  • MySQL string column types to distinguish 2011-03-07

    MySQL string column types to distinguish 1. First of all CHAR, VARCHAR, TEXT and BINARY, VARBINARY, BLOB difference: CHAR, VARCHAR, TEXT known as non-binary strings; BINARY, VARBINARY, BLOB as binary strings; Binary strings and the difference between

  • MySQL string functions in detail 2011-04-12

    MySQL string functions in detail 2009-11-4 Author: Editor: Lele Click to enter forum Keywords: MySQL string string or string (String) is zero or more characters of the finite sequence. Usually denoted by s = 'a1a2??? An' (n> = 0). It is the programmi

  • MySQL string functions intercept the string 2011-08-06

    MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is very powerful and flexible. 1. String interc

  • ORACLE string operations (transfer) 2011-08-09

    ORACLE string operations 1 connection string SQL> select 'abc' | | 'def' from dual; 'ABC' | ------ abcdef 2 lower SQL> select lower ('ABC012'); lower -------- abc012 3 caps select upper ('abc012'); upper -------- ABC012 4 left completion select lpad

  • Easy to use mysql string interception function 2011-09-01

    MySQL string interception function: left (), right (), substring (), substring_index (). There are mid (), substr (). One, mid (), substr () is equivalent to substring () function, substring () function is very powerful and flexible. 1 String interce

  • mysql connection string operations 2011-04-15

    CONCAT (str1, str2 ,...) Return link from the argument string. If any parameter is NULL, returns NULL. Can have more than 2 parameters. A numeric argument is converted to equivalent string. mysql> select CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql>

  • Own package ASP.NET, MYSQL database operations class 2010-03-26

    /** * Author : Beef brisket * Creation time :2010 March 7 When the day 17 35 Division * The class description : On MySQL database actions class */ using System; using System.Data; using MySql.Data.MySqlClient; namespace Niunan.BYLW.Utility { /// <sum

  • js string operations 2010-04-28

    First, create a string There are several ways to create a string. The simplest is to use quotation marks to a group of characters that contains up that can be assigned to a string variable. var myStr = "Hello, String!"; Can use double quotes or

  • Mysql string replace field 2010-05-31

    This paper is to use the replace function of mysql replace string. First look at the grammar explanations UPDATE tb1 SET f1 = REPLACEREPLACE (str, from_str, to_str) All appear in the string str string from_str were to_str replacement. In applications

  • [Change] mysql string function 2010-06-01

    Transfer from: http://www.phpv.net/html/1443.html The location for the string operation, the first location is marked as 1. ASCII (str) Return str string of ASCII characters in the most left code values. If str is the empty string, return 0. If str i

  • MySQL string split method to achieve 2010-06-07

    For example now have a string: 1,2,44,5,666,29232 It should be split into in accordance with commas: 1 2 44 5 666 29232 But also the total number of requests it. This I wrote before. But today's standard look. 1, the specific function SQL code DELIMI

  • [Change] Mysql date operations Daquan 2010-06-17

    For each type has a value range and the time value and specify the effective date of any description of the format the date and time types, see 7.3.6. Here is an example of using the date function. The following query select all the records, the date