SQL Server ±à³Ì(Ò») ×Ö·û´®´¦Àí
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®ºÍ±í´ïʽִÐв»Í¬µÄÔËËã¡£´ËÀຯÊý×÷ÓÃÓÚCHAR¡¢VARCHAR¡¢ BINARY¡¢ ºÍVARBINARY Êý¾ÝÀàÐÍÒÔ¼°¿ÉÒÔÒþʽת»»ÎªCHAR »òVARCHARµÄÊý¾ÝÀàÐÍ¡£¿ÉÒÔÔÚSELECT Óï¾äµÄSELECT ºÍWHERE ×Ó¾äÒÔ¼°±í´ïʽÖÐʹÓÃ×Ö·û´®º¯Êý¡£
Ò»¡¢×Ö·ûת»»º¯Êý
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() °Ñ×Ö·û´®Í·²¿µÄ¿Õ¸ñÈ¥µô¡£
2¡¢RTRIM() °Ñ×Ö·û´®Î²²¿µÄ¿Õ¸ñÈ¥µô¡£
Èý¡¢È¡×Ó´®º¯Êý
1¡¢left()
LEFT (<character_expression>£¬ <integer_expression>)
·µ»Øcharacter_expression ×óÆð integer_expression ¸ö×Ö·û¡£
2¡¢RIGHT()
RIGHT (<character_expression>£¬ <integer_expression>)
·µ»Øcharacter_expression ÓÒÆð integer_expression ¸ö×Ö·û¡£
3¡¢SUBSTRING()
SUBSTRING (<expression>£¬ <starting_ position>£¬ length)
·µ»Ø´Ó×Ö·û´®×ó±ßµÚstarting_ position ¸ö×Ö·ûÆðlength¸ö×Ö·ûµÄ²¿·Ö¡£
ËÄ¡¢×Ö·û´®±È½Ïº¯Êý
1¡¢CHARINDEX()
·µ»Ø×Ö·û´®ÖÐij¸öÖ¸¶¨µÄ×Ó´®³öÏֵĿªÊ¼Î»Öá£
CHARINDEX (<’substring_expression’>£¬ <expression>)
ÆäÖÐsubstring _expression ÊÇËùÒª²éÕÒµÄ×Ö·û±í´ïʽ£¬expression ¿ÉΪ×Ö·û´®Ò²¿ÉΪÁÐÃû±í´ïʽ¡£Èç¹ûûÓз¢ÏÖ×Ó´®£¬Ôò·µ»Ø0 Öµ¡£
´Ëº¯Êý²»ÄÜÓÃÓÚTEXT ºÍIMAGE Êý¾ÝÀàÐÍ¡£
2¡¢PATINDEX()
·µ»Ø×Ö·û´®ÖÐij¸öÖ¸¶¨µÄ×Ó´®³öÏֵĿªÊ¼Î»Öá£
PATINDEX (<’%substring _expression%’>£¬
Ïà¹ØÎĵµ£º
¡¡Ç°Ì᣺°²×°sqlserver£¬°²×°outlook£¬outlookÒѾÕýÈ·ÅäÖá£
£¨1£©ÅäÖÃsqlserverµÄÆô¶¯Óû§ÎªÓòÓû§¡£sqlserverÆô¶¯Ä¬Èϵı¾µØÕË»§£¬±¾µØÕË»§Æô¶¯µÄ·þÎñÊÇÎÞ·¨Íê³ÉÓʼþµÄ·¢ËͽÓÊܹ¦Äܵġ£
ÅäÖÃÓòÓû§Æô¶¯£º´ò¿ª¿ØÖÆÃæ°åµÄ--¹ÜÀí¹¤¾ß--·þÎñ£¬ÕÒµ½sqlserver·þÎñ£¬²é¿´ÊôÐÔ¡£Ô ......
ÈÕÖ¾´«ËÍÊÇSQL SERVER2000ÆóÒµ°æ½øÐÐË«»úÈȱ¸µÄÒ»¸öÖ÷Òª½â¾ö·½°¸£¬ÆäÔÀí±È½Ï¼òµ¥£¬ÊµÏÖÒ²±È½Ï·½±ã£¬Ö»ÊÇÓÉÓÚºÍWindowsÖ®¼ä´í×Û¸´ÔӵĹØϵ£¬µ¼ÖÂÉèÖÃÆðÀ´Óв»ÉÙÀ§ÄÑ£¬³öÏÖÎÊÌâÒ²²»ÈÝÒ×Õï¶Ï¡£
1.ʵÏÖ¸ÅÊö
*Ë«»úÈȱ¸Õë¶ÔµÄÊǾßÌåµÄij¸öÊý¾Ý¿â¶ø²»ÊÇÕû¸ö·þÎñÆ÷
Ë«» ......
select case when b.name is null and c.name is null then 'ºÏ¼Æ' when b.name is null and c.name is not null then 'С¼Æ' else b.name end as mtrname,
sum(a.number),c.name as cname from x_sell a join x_material b on a.mtr=b.fid join p_organi c on c.fid=a.customer
where a.stime>'2009-10-01'
GROUP B ......
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ÔÚ×ösqlÌâʱ£¬ÎÒÃÇ×îÅÂÓöµ½Ìõ¼þ¸´ÔӵIJéѯÓï¾ä£¬ÒòΪ´ó¶àʱºòÐèÒªÁ½Èý¸ö×Ó²éѯÀ´ÊµÏÖ£¬
¶ø´ó¶àÊýͬѧ£¬¿ÉÒÔ˵¶¼±È½ÏÅÂ×Ó²éѯµÄ£¬ÒÔÇ°ÎÒÒ²±»ÕâЩÌâĿŪµÄÔÆÀïÎíÀïµÄ£¬
²»¹ý×ö¶àÁËÕâÀàµÄÌâÄ¿ºó·¢ÏÖ£¬ÆäʵҲÓÐÒ»¶¨¹æÂÉ£¬ÎÒÃÇ¿ÉÒÔ×ܽáÒ»Ì×ÊʺÏ×Ô¼ºµÄ·½·¨À´´¦Àí£¬
ÏÂÃæʱÎÒ¶Ô¸ÃÀàÌâÄ¿µÄ´¦Àí·½·¨£º
ÓйºÎï±íÈçÏ£º
ÌâÄ¿£º ²é ......