http://www.mysqlkorea.co.kr
ѱ۸Ŵ 5.0 , ѱ۸Ŵ 5.1 , MySQL 5.1 HA , ڸŴ
Advanced Knowle...  
Ͼ Ʈ  
α  
۷ι MySQL  
MySQL 5.5 GA  
MySQL 5.6 Developer  
ֽű
mysql ؼ
killed ° ʡ
mysql /
apm ġ ް
 
ѱ۸Ŵ 5.0 > Ŵ > ѱ۸Ŵ 5.0
 

12.3. Ʈ Լ

 

  12.3.1. Ʈ Լ

 

Ʈ- Լ ̰ max_allowed_packet ý Ŭ 쿡, NULL Ѵ. Section 7.5.2, Ķ Ʃϱ .

 

Ʈ (position) ϴ Լ , ù ° 1 ȴ.

  • ASCII(str)

Ʈ str Ѵ. str Ʈ(empty string) 0 Ѵ. NULL if str NULLNULL Ѵ. ASCII() 0 255 ڿ ؼ Ѵ.

 

mysql> SELECT ASCII('2');

        -> 50

mysql> SELECT ASCII(2);

        -> 50

mysql> SELECT ASCII('dx');

        -> 100

 

ORD() Լ Բ .

  • BIN(N)

N ̳ʸ Ʈ ǥ ϴµ, N (BIGINT) ̴. ̰ CONV(N,10,2) ϴ. N NULLNULL Ѵ.

 

mysql> SELECT BIN(12);

        -> '1100'

  • BIT_LENGTH(str)

Ʈ str ̸ Ʈ Ѵ.

 

mysql> SELECT BIT_LENGTH('text');

        -> 32

  • CHAR(N,... [USING charset_name])

CHAR() μ N · ؼ ϰ ̷ ڵ ־ ڷ Ʈ Ѵ. NULL (skipped)ȴ.

 

mysql> SELECT CHAR(77,121,83,81,'76');

        -> 'MySQL'

mysql> SELECT CHAR(77,77.3,'77.3');

        -> 'MMM'

 

 

MySQL 5.0.15 ,  255 ū CHAR() μ Ʈ ȯȴ. , CHAR(256) CHAR(1,0) , ׸ CHAR(256*256) CHAR(1,0,0) :

 

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));

+----------------+----------------+

| HEX(CHAR(1,0)) | HEX(CHAR(256)) |

+----------------+----------------+

| 0100           | 0100           |

+----------------+----------------+

mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));

+------------------+--------------------+

| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |

+------------------+--------------------+

| 010000           | 010000             |

+------------------+--------------------+

 

Ʈδ, CHAR() ̳ʸ Ʈ Ѵ. ־ ¿ ִ Ʈ ؼ, USING ɼ Ѵ:

 

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));

+---------------------+--------------------------------+

| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |

+---------------------+--------------------------------+

| binary              | utf8                           |

+---------------------+--------------------------------+

 

USING Ͽµ Ʈ ־ ¿ ؼ ȿ ʰ Ǹ, ȴ. , ƮƮ(strict) SQL 带 Ȱȭ Ѵٸ, CHAR() NULL ȴ.

MySQL 5.0.15 , CHAR() ¿ ִ Ʈ ϸ USING ȴ. , μ (modulo) 256 ؼ DZ , CHAR(256) CHAR(256*256) CHAR(0) ȴ.

  • CHAR_LENGTH(str)

Ʈ str ̸ Ѵ. Ʈ ڴ ڷ (count) ȴ. , 5 2 Ʈ ڸ Ʈ ؼ, LENGTH() 10 , CHAR_LENGTH() 5 Ѵ.

  • CHARACTER_LENGTH(str)

CHARACTER_LENGTH() CHAR_LENGTH() ϴ.

  • CONCAT(str1,str2,...)

μ Ʈ Ѵ. Ǵ ̻ μ ִ. μ ̳ʸ Ʈ ƴ϶, ̳ʸ ƴ Ʈ ȴ. μ ̳ʸ Ʈ ԵǾ ִٸ, ̳ʸ Ʈ ȴ. μ ̳ʸ Ʈ · ȯȴ; ̷ ȯǴ 쿡, Ʒ Ȯϰ Ÿ ijƮ(type cast) ϸ ȴ:

 

        SELECT CONCAT(CAST(int_col AS CHAR), char_col);

 

CONCAT() μ NULL ƴϸ NULL Ѵ.

 

mysql> SELECT CONCAT('My', 'S', 'QL');

        -> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');

        -> NULL

mysql> SELECT CONCAT(14.3);

        -> '14.3'

  • CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() ڸ ϴ (Concatenate With Separator) Ÿ ̸ CONCAT() Ư ̴. ù ° μ μ鿡 (separator) ȴ. ڴ Ǵ Ʈ ̿ ߰ȴ. ڴ Ʈ ִ. ڰ NULL ̸, NULL̴.

 

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');

        -> 'First name,Second name,Last Name'

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');

        -> 'First name,Last Name'

 

 

CONCAT_WS() Ʈ(empty string) (skip) ʴ´. , μ NULL ø Ѵ.

  • CONV(N,from_base,to_base)

ٸ ̽(base) ڸ ȯѴ. from_base ̽ to_base ȯ N Ʈ ǥ Ѵ. μ ϳ NULL̸ NULL Ѵ.μ N ؼ, Ǵ Ʈ ִ. ּ ̽ 2 ̰ ִ ̽ 36̴. to_base , N ȣȭ ڷ ֵȴ. ׷ 쿡, N ȣ ڷ ޵ȴ. CONV() 64-Ʈ е ´.

 

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'

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

N = 1̸, str1, N = 2̸, str2 ϴ . N 1 ۰ų Ǵμ ں 쿡 NULL Ѵ. ELT() FIELD() (complement)̴.

 

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');

        -> 'ej'

mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');

        -> 'foo'

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

bits ִ Ʈ (bit set) ؼ, on Ʈ, ׸ Ʈ ؼ, off Ʈ Ѵ. bits ִ Ʈ ʿ ˻ȴ ( Ʈ). Ʈ ʿ ߰Ǵµ, separator Ʈ ȴ (Ʈ Ʈ ޸ ,). ˻簡 Ǵ Ʈ ڴ number_of_bits ־ (Ʈ 64).

 

mysql> SELECT EXPORT_SET(5,'Y','N',',',4);

        -> 'Y,N,Y,N'

mysql> SELECT EXPORT_SET(6,'1','0',',',10);

        -> '0,1,1,0,0,0,0,0,0,0'

 

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

str1, str2, str3, ... Ʈ ִ str ε() Ѵ. str ã 쿡 0 Ѵ.

FIELD() μ Ʈ̶, μ Ʈ 񱳵ȴ. μ 쿡 ڷ 񱳰 ȴ. ׷ , μ (double) 񱳰 ȴ.

str NULL̶, 0 Ǵµ, NULL  񱳰 ʱ ̴. FIELD() ELT() (complement)̴.

 

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

        -> 2

mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');

        -> 0

  • FIND_IN_SET(str,strlist)

Ʈ str N Ʈ Ʈ Ʈ strlist ִ ̶, 1 ִ N Ѵ. ϳ Ʈ Ʈ ޸ ڷ е Ʈ Ʈ̴. ù ° μ Ʈ̰ ° μ Ÿ SET ÷̶, FIND_IN_SET() Լ Ʈ ϱ ؼ ȭ ȴ. str strlist ų Ǵ strlist Ʈ̶, 0 Ѵ. NULL if μ ϳ NULL̶, NULL Ѵ. ù ° μ ޸ ڸ ִٸ, Լ ʴ´.

 

 

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

        -> 2

  • FORMAT(X,D)

X '#,###,###.##' · , D Ҽ ڸ (round) ϰ, Ʈ Ѵ. D 0 ̸, Ҽ Ǵ м κ ʴ´.

 

mysql> SELECT FORMAT(12332.123456, 4);

        -> '12,332.1235'

mysql> SELECT FORMAT(12332.1,4);

        -> '12,332.1000'

mysql> SELECT FORMAT(12332.2,0);

        -> '12,332' 

  • HEX(N_or_S)

N_or_S ڶ, N 16 شϴ Ʈ ǥ ϴµ, N (BIGINT) ڰ ȴ. ̰ CONV(N,10,16) ϴ.

N_or_S Ʈ ̶, N_or_S 16 Ʈ ǥ ϴµ, N_or_S ִ ڴ 2 16 ڷ ȯȴ.

 

mysql> SELECT HEX(255);

        -> 'FF'

mysql> SELECT 0x616263;

        -> 'abc'

mysql> SELECT HEX('abc');

        -> 616263

  • INSERT(str,pos,len,newstr)

str pos ϴ Ʈ len ̸ŭ Ʈ newstr Ѽ str Ѵ. pos Ʈ ȿ ʴٸ, Ʈ Ѵ. len Ʈ ʴٸ, pos   Ʈ ü Ų.  μ NULL ̸, NULL Ѵ.

 

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');

        -> 'QuWhattic'

mysql> SELECT INSERT('Quadratic', -1, 4, 'What');

        -> 'Quadratic'

mysql> SELECT INSERT('Quadratic', 3, 100, 'What');

        -> 'QuWhat'

 

Լ Ʈ Ѵ.

  • INSTR(str,substr)

str Ʈ substr ó Ѵ. ̰ LOCATE() μ ϴ Ͱ , μ ݴ밡 ȴ.

 

mysql> SELECT INSTR('foobarbar', 'bar');

        -> 4

mysql> SELECT INSTR('xbar', 'foobar');

        -> 0

 

Լ Ʈ ϸ, ּ ϳ μ ̳ʸ Ʈ 쿡 ũ⸦ Ѵ.

  • LCASE(str)

LCASE() LOWER() Ǿ ̴.

  • LEFT(str,len)

Ʈ str κ len ڸ Ѵ.

 

mysql> SELECT LEFT('foobarbar', 5);

        -> 'fooba' 

  • LENGTH(str)

str ̸ Ʈ Ѵ. Ʈ ڴ Ʈ (count)ȴ. ̰ 2 Ʈ ڰ 5 ִ Ʈ , LENGTH() 10 ϴ ݸ鿡, CHAR_LENGTH() 5 Ѵٴ ǹѴ.

 

mysql> SELECT LENGTH('text');

        -> 4

  • LOAD_FILE(file_name)

빰 Ʈ Ѵ. Լ ϱ ؼ, ݵ ȣƮ ־ ϸ, Ͽ ̸ ü ؾ ϸ, ׸ FILE Ѵ. ڿ ־ ϸ ũ max_allowed_packet Ʈ ٴ ۾ƾ Ѵ.

ʰų Ǵ ߿ ϳ ȴٸ, Լ NULL Ѵ.

MySQL 5.0.19 , character_set_filesystem ý ͷ Ʈ ־ ̸ ؼ Ѵ.

 

mysql> UPDATE t

            SET blob_col=LOAD_FILE('/tmp/picture')

            WHERE id=1;

  • LOCATE(substr,str), LOCATE(substr,str,pos)

ù ° ؽ str Ʈ substr ó Ѵ. ° ؽ, pos ؼ, str Ʈ substr ó Ѵ. substr str, 0 Ѵ.

 

mysql> SELECT LOCATE('bar', 'foobarbar');

        -> 4

mysql> SELECT LOCATE('xbar', 'foobar');

        -> 0

mysql> SELECT LOCATE('bar', 'foobarbar', 5);

        -> 7 

 

Լ Ʈ ϸ, μ ߿ ּ ϳ ̳ʸ Ʈ 쿡 ũ⸦ Ѵ.

  • LOWER(str)

(current) ο Ʈ str ڸ ҹڷ Ѽ Ѵ. Ʈ latin1 (cp1252 West European).

 

mysql> SELECT LOWER('QUADRATICALLY');

        -> 'quadratically'

 

Լ Ʈ Ѵ.

  • LPAD(str,len,padstr)

ʿ Ʈ padstr ־ ̸ len ŭ ǵ Ʈ str Ѵ. str len ٸ, len ŭ پ .

 

mysql> SELECT LPAD('hi',4,'??');

        -> '??hi'

mysql> SELECT LPAD('hi',1,'??');

        -> 'h'

  • LTRIM(str)

Ʈ տ ִ ̽ Ʈ str Ѵ.

 

mysql> SELECT LTRIM('  barbar');

        -> 'barbar'

 

 

Լ Ʈ Ѵ.

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

bits ¿ Ǵ Ʈ ִ Ʈ (޸ ڷ е Ʈ ִ Ʈ) Ѵ. str1 Ʈ 0 ǰ, str2 1 Ǹ, ̷ ȴ. str1, str2, ... ִ NULL ʴ´.

 

mysql> SELECT MAKE_SET(1,'a','b','c');

        -> 'a'

mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');

        -> 'hello,world'

mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');

        -> 'hello'

mysql> SELECT MAKE_SET(0,'a','b','c'); 

  • MID(str,pos,len)

MID(str,pos,len) SUBSTRING(str,pos,len) Ǿ ̴.

  • OCT(N)

N 8 شϴ Ʈ ǥ ϴµ, N (BIGINT) ȣ ȴ. ̰ CONV(N,10,8) . N NULL̸, NULL Ѵ.

 

mysql> SELECT OCT(12);

        -> '14'

  • OCTET_LENGTH(str)

OCTET_LENGTH() LENGTH() Ǿ ̴.

  • ORD(str)

If the leftmost character of the stringƮ str ڰ Ʈ ڶ, ڿ ڵ带 ϴµ, Ʒ ؼ :

 

  (1st byte code)

+ (2nd byte code 256)

+ (3rd byte code 2562) ...  

 

ڰ Ʈ ڰ ƴ϶, ORD() ASCII() Լ Ѵ.

 

mysql> SELECT ORD('2');

        -> 50 

  • POSITION(substr IN str)

POSITION(substr IN str) LOCATE(substr,str) Ǿ ̴.

 

  • QUOTE(str)

SQL ɹ ̽ ִ Ʈ ο ȣ ش. μ NULL̸, ο ȣ ܾ NULL ȴ.

 

mysql> SELECT QUOTE('Don\'t!');

        -> 'Don\'t!'

mysql> SELECT QUOTE(NULL);

        -> NULL 

  • REPEAT(str,count)

count Ƚ ŭ ݺ Ʈ str Ʈ Ѵ. count 1 , Ʈ Ѵ. str Ǵ count NULL̶, NULL Ѵ

 

mysql> SELECT REPEAT('MySQL', 3);

        -> 'MySQLMySQLMySQL'

 

  • REPLACE(str,from_str,to_str)

Ʈ from_str κ Ʈ to_str üؼ Ʈ str Ѵ.               REPLACE() from_str ˻ ũ⸦ ؼ Ī Ѵ.

 

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

        -> 'WwWwWw.mysql.com'

 

Լ Ʈ Ѵ.

  • REVERSE(str)

켭 Ʈ str Ѵ.

 

mysql> SELECT REVERSE('abc');

        -> 'cba'

 

Լ Ʈ Ѵ.

  • RIGHT(str,len)

str κ len ŭ Ѵ..

 

mysql> SELECT RIGHT('foobarbar', 4);

        -> 'rbar'

 

 

Լ Ʈ Ѵ.

  • RPAD(str,len,padstr)

Ʈ padstr ʿ ־ len ŭ str  Ѵ. str len ٸ, len ŭ ª.

 

mysql> SELECT RPAD('hi',5,'?');

        -> 'hi???'

mysql> SELECT RPAD('hi',1,'?');

        -> 'h'

 

Լ Ʈ Ѵ.

  • RTRIM(str)

Ʈ str ڿ پ ִ ̽ ڸ Ʈ Ѵ.

 

mysql> SELECT RTRIM('barbar   ');

        -> 'barbar'

 

Լ Ʈ Ѵ.

  • SOUNDEX(str)

Str(soundex) Ʈ Ѵ. Ҹ Ʈ  Ʈ Ѵ. ǥ  Ʈ 4 ̰ , SOUNDEX() Լ Ʈ Ѵ. ǥ  Ʈ ؼ SUBSTRING() ִ. str ִ ߿ ĺ ƴ ͵ ð ȴ. A-Z  ڵ ޵ȴ.

 

mysql> SELECT SOUNDEX('Hello');

        -> 'H400'

mysql> SELECT SOUNDEX('Quadratically');

        -> 'Q36324'

  • expr1 SOUNDS LIKE expr2

̰ SOUNDEX(expr1) = SOUNDEX(expr2) .

  • SPACE(N)

N ̽ ڷ Ʈ Ѵ.

 

mysql> SELECT SPACE(6);

        -> '      ' 

  • SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

len μ ´ pos ϴ Ʈ Ʈ str Ѵ. len μ ִ ´ pos ؼ Ʈ len ڸ Ѵ. FROM ϴ ´ ǥ SQL ؽ̴. pos ϴ ͵ ϴ. , Ʈ Ʈ ó ƴ pos ġ ȴ. Լ  ¿ ϴ.

 

mysql> SELECT SUBSTRING('Quadratically',5);

        -> 'ratically'

mysql> SELECT SUBSTRING('foobarbar' FROM 4);

        -> 'barbar'

mysql> SELECT SUBSTRING('Quadratically',5,6);

        -> 'ratica'       

mysql> SELECT SUBSTRING('Sakila', -3);

        -> 'ila'       

mysql> SELECT SUBSTRING('Sakila', -5, 3);

        -> 'aki'

mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);

        -> 'ki'

 

Լ Ʈ Ѵ.

len 1 , Ʈ ȴ.

SUBSTR() SUBSTRING() ϴ.

  • SUBSTRING_INDEX(str,delim,count)

(delimiter) delim count ŭ Ʈ str Ʈ Ѵ. count (positive), ʿ ִ ͵ ϵȴ (ʺ ). count , ʿ ִ ͵ ϵȴ (ʺ ). SUBSTRING_INDEX() delim ˻ ũ⸦ Ѵ.

 

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

        -> 'www.mysql'

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

        -> 'mysql.com'

 

Լ Ʈ Ѵ.

  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

Ʈ str remstr λ (prefixes) Ǵ ̻縦 · Ѵ. BOTH, LEADING, Ǵ TRAILING ͵ ־ ʴ´ٸ, BOTH ־ ֵȴ. remstr ̸, , ̽ ŵȴ.

 

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'

 

Լ Ʈ Ѵ.

  • UCASE(str)

UCASE() UPPER() Ǿ̴.

  • UNHEX(str)

HEX(str)ʹ ݴ Ѵ. , Լ μ ִ 16 ڷ ؼ ϰ ڿ ϴ ڷ ȯ Ų. ڵ ̳ʸ Ʈ ϵȴ.

 

mysql> SELECT UNHEX('4D7953514C');

        -> 'MySQL'

mysql> SELECT 0x4D7953514C;

        -> 'MySQL'

mysql> SELECT UNHEX(HEX('string'));

        -> 'string'

mysql> SELECT HEX(UNHEX('1267'));

        -> '1267' 

  • UPPER(str)

Returns Ʈ str (current) ο 빮ڷ ȯ ڿ Բ Ѵ. Ʈ latin1 (cp1252 West European).

 

mysql> SELECT UPPER('Hej');

        -> 'HEJ' 

 

Լ Ʈ Ѵ.

12.3. Ʈ Լ
MySQL Korea Ʈ ()̺ Ƿ 縦 մϴ.
2010-2011 ssebiz All Rights Reserved.