[ת]SQL½ØÈ¡×Ö·û´®
SUBSTRING
·µ»Ø×Ö·û¡¢binary¡¢text »ò image ±í´ïʽµÄÒ»²¿·Ö¡£ÓйؿÉÓë¸Ãº¯ÊýÒ»ÆðʹÓõÄÓÐЧ Microsoft® SQL Server™ Êý¾ÝÀàÐ͵ĸü¶àÐÅÏ¢£¬Çë²Î¼ûÊý¾ÝÀàÐÍ¡£
Óï·¨
SUBSTRING ( expression , start , length )
²ÎÊý
expression
ÊÇ×Ö·û´®¡¢¶þ½øÖÆ×Ö·û´®¡¢text¡¢image¡¢Áлò°üº¬Áеıí´ïʽ¡£²»ÒªÊ¹Óðüº¬¾ÛºÏº¯ÊýµÄ±í´ïʽ¡£
start
ÊÇÒ»¸öÕûÊý£¬Ö¸¶¨×Ó´®µÄ¿ªÊ¼Î»Öá£
length
ÊÇÒ»¸öÕûÊý£¬Ö¸¶¨×Ó´®µÄ³¤¶È£¨Òª·µ»ØµÄ×Ö·ûÊý»ò×Ö½ÚÊý£©¡£ substring()
——ÈÎÒâλÖÃÈ¡×Ó´®
left()
right()
——×óÓÒÁ½¶ËÈ¡×Ó´®
ltrim()
rtrim()
——½Ø¶Ï¿Õ¸ñ£¬Ã»ÓÐtrim()¡£
charindex()
patindex()
——²é×Ó´®ÔÚĸ´®ÖеÄλÖã¬Ã»Óзµ»Ø0¡£Çø±ð£ºpatindexÖ§³ÖͨÅä·û£¬charindex²»Ö§³Ö¡£ º¯Êý¹¦Ð§£º
×Ö·û´®½ØÈ¡º¯Êý£¬Ö»ÏÞµ¥×Ö½Ú×Ö·ûʹÓ㨶ÔÓÚÖÐÎĵĽØȡʱÓöÉÏÆæÊý³¤¶ÈÊÇ»á³öÏÖÂÒÂ룬ÐèÁíÐд¦Àí£©£¬±¾º¯Êý¿É½ØÈ¡×Ö·û´®Ö¸¶¨·¶Î§ÄÚµÄ×Ö·û¡£
Ó¦Ó÷¶Î§£º
±êÌâ¡¢ÄÚÈݽØÈ¡
º¯Êý¸ñʽ£º
string substr ( string string, int start [, int length])
²ÎÊý1£º´¦Àí×Ö·û´®
²ÎÊý2£º½ØÈ¡µÄÆðʼλÖ㨵ÚÒ»¸ö×Ö·ûÊÇ´Ó0¿ªÊ¼£©
²ÎÊý3£º½ØÈ¡µÄ×Ö·ûÊýÁ¿
substr()¸ü¶à½éÉÜ¿ÉÔÚPHP¹Ù·½ÊÖ²áÖвéѯ£¨×Ö·û´®´¦Àíº¯Êý¿â£©
¾ÙÀý£º
substr("ABCDEFG", 0); //·µ»Ø£ºABCDEFG£¬½ØÈ¡ËùÓÐ×Ö·û
substr("ABCDEFG", 2); //·µ»Ø£ºCDEFG£¬½ØÈ¡´ÓC¿ªÊ¼Ö®ºóËùÓÐ×Ö·û
substr("ABCDEFG", 0, 3); //·µ»Ø£ºABC£¬½ØÈ¡´ÓA¿ªÊ¼3¸ö×Ö·û
substr("ABCDEFG", 0, 100); //·µ»Ø£ºABCDEFG£¬100ËäÈ»³¬³öÔ¤´¦ÀíµÄ×Ö·û´®×¶È£¬µ«²»»áÓ°Ïì·µ»Ø½á¹û£¬Ï
Ïà¹ØÎĵµ£º
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍ ......
ʮһ¡¢ÒÔÉϺ¯ÊýµÄ²¿·ÖʵÀý
1:replace º¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸ö²ÎÊýÄãÏëÌæ»»µÄ²¿·Ö£¬µÚÈý¸ö²ÎÊýÄãÒªÌæ»»³Éʲô
select replace('lihan','a','b')
& ......
SQLº¯ÊýÖ®ËÄÉáÎåÈ루ת×Ôhttp://ln1058.javaeye.com/blog/191502£©
ÎÊÌâ1£º
SELECT CAST('123.456' as decimal) ½«»áµÃµ½ 123£¨Ð¡ÊýµãºóÃæµÄ½«»á±»Ê¡ÂÔµô£©¡£
Èç¹ûÏ£ÍûµÃµ½Ð¡ÊýµãºóÃæµÄÁ½Î»¡£
ÔòÐèÒª°ÑÉÏÃæµÄ¸ÄΪ
SELECT CAST('123.456' as decimal(38, 2)) ===>123.46
×Ô¶¯ËÄÉáÎåÈëÁË£ ......
SQLÓï¾äµ¼Èëµ¼³ö´óÈ«
/******* µ¼³öµ½excel
EXEC master..xp_cmdshell ¡¯bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""¡¯
/*********** µ¼ÈëExcel
SELECT *
from OpenDataSource( ¡¯Microsoft.Jet.OLEDB.4.0¡¯,
¡¯Data Source="c:\test.xls ......