Sql Server»ù±¾º¯Êý
1.×Ö·û´®º¯Êý
³¤¶ÈÓë·ÖÎöÓÃ
datalength(Char_expr) ·µ»Ø×Ö·û´®°üº¬×Ö·ûÊý,µ«²»°üº¬ºóÃæµÄ¿Õ¸ñ
substring(expression,start,length) ²»¶à˵ÁË,È¡×Ó´®
right(char_expr,int_expr) ·µ»Ø×Ö·û´®ÓÒ±ßint_expr¸ö×Ö·û
×Ö·û²Ù×÷Àà
upper(char_expr) תΪ´óд
lower(char_expr) תΪСд
space(int_expr) Éú³Éint_expr¸ö¿Õ¸ñ
replicate(char_expr,int_expr)¸´ÖÆ×Ö·û´®int_expr´Î
reverse(char_expr) ·´×ª×Ö·û´®
stuff(char_expr1,start,length,char_expr2) ½«×Ö·û´®char_expr1ÖеĴÓ
start¿ªÊ¼µÄlength¸ö×Ö·ûÓÃchar_expr2´úÌæ
ltrim(char_expr) rtrim(char_expr) È¡µô¿Õ¸ñ
ascii(char) char(ascii) Á½º¯Êý¶ÔÓ¦,È¡asciiÂë,¸ù¾ÝasciiÂðÈ¡×Ö·û
×Ö·û´®²éÕÒ
charindex(char_expr,expression) ·µ»Øchar_exprµÄÆðʼλÖÃ
patindex("%pattern%",expression) ·µ»ØÖ¸¶¨Ä£Ê½µÄÆðʼλÖÃ,·ñÔòΪ0
2.Êýѧº¯Êý
abs(numeric_expr) Çó¾ø¶ÔÖµ
ceiling(numeric_expr) È¡´óÓÚµÈÓÚÖ¸¶¨ÖµµÄ×îСÕûÊý
exp(float_expr) È¡Ö¸Êý
floor(numeric_expr) СÓÚµÈÓÚÖ¸¶¨ÖµµÃ×î´óÕûÊý
pi() 3.1415926.........
power(numeric_expr,power) ·µ»Øpower´Î·½
rand([int_expr]) Ëæ»úÊý²úÉúÆ÷
round(numeric_expr,int_expr) °²int_expr¹æ¶¨µÄ¾«¶ÈËÄÉáÎåÈë
sign(int_expr) ¸ù¾ÝÕýÊý,0,¸ºÊý,,·µ»Ø+1,0,-1
sqrt(float_expr) ƽ·½¸ù
////////////////////////////////////////////////////////////////////
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍUPPER()
LOWER()½«×Ö·û´®È«²¿×ªÎªÐ¡Ð´£»UPPER()½«×Ö·û´®È«²¿×ªÎª´óд¡£
4¡¢STR()
°ÑÊýÖµÐÍÊý¾Ýת»»Îª×Ö·ûÐÍÊý¾Ý¡£
STR (<float_expression>[£¬length[£¬ <decimal>]])
length Ö¸¶¨·µ»ØµÄ×Ö·û´®µÄ³¤¶È£¬decimal Ö¸¶¨·µ»ØµÄСÊýλÊý¡£Èç¹ûûÓÐÖ¸¶¨³¤¶È£¬È±Ê¡µÄlength ֵΪ10£¬ decimal ȱʡֵΪ0¡£
µ±length »òÕßdecimal Ϊ¸ºÖµÊ±£¬·µ»ØNULL£»
µ±length СÓÚСÊýµã×ó±ß£¨°üÀ¨·ûºÅ룩µÄλÊýʱ£¬·µ»Ølength ¸ö*£»
ÏÈ·þ´Ólength £¬ÔÙÈ¡decimal £»
µ±·µ»ØµÄ×Ö·û´®Î»ÊýСÓÚlength £¬×ó±ß²¹×ã¿Õ¸ñ¡£
¶þ¡¢È¥¿Õ¸ñº¯Êý
1¡¢LTRIM() °Ñ×
Ïà¹ØÎĵµ£º
1.¶Ô²éѯ½øÐÐÓÅ»¯£¬Ó¦¾¡Á¿±ÜÃâÈ«±íɨÃ裬Ê×ÏÈÓ¦¿¼ÂÇÔÚ where ¼° order by Éæ¼°µÄÁÐÉϽ¨Á¢Ë÷Òý¡£
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ
·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃ
ĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâ
Ñù²éѯ£º
sel ......
¾³£»á¿´¼ûÔÚSQL³ÌÐòµÄ¿ªÍ·ÓÐÕâÑùÒ»¾ä»°
if OBJECT_ID('tb') is not null
drop table tb
º¯ÊýÓï·¨ÊÇÕâÑù£º
int OBJECT_ID('objectname');
×÷ÓÃÊÇ¿´¶ÔÏóobjectnameÊÇ·ñ´æÔÚ¡£
ÆäÖвÎÊýobjectname±íʾҪʹÓõĶÔÏó£¬ÊÇchar»òÕßncharÀàÐÍ¡£
·µ»ØÖµÀàÐÍΪint£¬Èç¹û¶ÔÏó´æÔÚ£¬Ôò·µ»Ø´Ë¶ÔÏóÔÚϵͳÖеı ......
--²âÊÔÊý¾Ý
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'ɽ¶«Ê¡'
UNION ALL SELECT '002','001','ÑĮ̀ÊÐ'
UNION ALL SELECT '004','002','ÕÐÔ¶ÊÐ'
UNION ALL SELECT '003','001','ÇൺÊÐ'
UNION ALL SELECT '00 ......
ÎÒ¶¨ÒåÁËÒ»¸ö textarea:
<tr bgcolor="#CCCCCC" align="center" valign="bottom">
<td align="right" valign="top" class="style1" >ÎÄÕÂÄÚÈÝ£º</td>
&nbs ......
'SQL·À×¢È뺯Êý£¬µ÷Ó÷½·¨£¬ÔÚÐèÒª·À×¢ÈëµÄµØ·½Ìæ»»ÒÔÇ°µÄrequest("XXXX")ΪSafeRequest("XXXX")
'www.yongfa365.com
Function
SafeRequest(ParaValue)
ParaValue =
Trim
(
Request
(Pa ......