sqlserverµÄ¼¸¸öº¯ÊýÒª¼Ç¼
½ñÈÕÅöµ½¸öÎÊÌ⣺ҪʵÏÖÊý¾Ý±íÖеÄÒ»¸ö×Ö¶ÎÖеÄÎı¾Îª"xxx.gif"µÄת»»Îª"xxx.jpg",ÎÒ²»ÖªµÀÆä¾ßÌåÃû³Æ£¬Ö»ÖªµÀÊÇÒÔgif½áβ¡£
ÎÊÌâ½â¾ö£ºupdate pet set petPhoto=substring(petPhoto,1,datalength(petPhoto)-3)+'jpg' where petPhoto like '%.gif'
×¢ÒâÆ¥Åä·û£º“%”ΪƥÅäÈÎÒⳤ¶ÈÈÎÒâ×Ö·û,“_”Æ¥Åäµ¥¸öÈÎÒâ×Ö·û£¬[A]Æ¥ÅäÒÔA¿ªÍ·µÄ£¬[^A]Æ¥Åä³ý¿ªÒÔA¿ªÍ·µÄ¡£ÖªµÀº¯ÊýÊǽâ¾öÎÊÌâµÄ¹Ø¼ü£¨ÒÔÏÂת×ÔÍøÂ磩£º
1£¬Í³¼Æº¯Êý avg, count, max, min, sum
2£¬ Êýѧº¯Êý
ceiling£¨n) ·µ»Ø´óÓÚ»òÕßµÈÓÚnµÄ×îСÕûÊý
floor(n), ·µ»ØСÓÚ»òÕßÊǵÈÓÚnµÄ×î´óÕûÊý
round(m,n), ËÄÉáÎåÈë,nÊDZ£ÁôСÊýµÄλÊý
abs(n) ¾ø¶ÔÖµ
sign(n), µ±n>0, ·µ»Ø1£¬n=0,·µ»Ø0£¬n<0, ·µ»Ø-1
PI(), 3.1415....
rand(),rand(n), ·µ»Ø0-1Ö®¼äµÄÒ»¸öËæ»úÊý
3£¬×Ö·û´®º¯Êý
ascii(), ½«×Ö·ûת»»ÎªASCIIÂë, ASCII('abc') = 97
char(), ASCII Âë ת»»Îª ×Ö·û
low()£¬upper() ´óСдת»»
str(a,b,c)ת»»Êý×ÖΪ×Ö·û´®¡£ a,ÊÇҪת»»µÄ×Ö·û´®¡£bÊÇת»»ÒÔºóµÄ³¤¶È£¬cÊÇСÊýλÊý¡£str(123.456,8,2) = 123.46
ltrim(), rtrim() È¥¿Õ¸ñ ltrimÈ¥×ó±ßµÄ¿Õ¸ñ,rtrimÈ¥ÓұߵĿոñ
left(n), right(n), substring(str, start,length) ½ØÈ¡×Ö·û´®
charindex(×Ó´®£¬Ä¸´®£©£¬²éÕÒÊÇ·ñ°üº¬¡£ ·µ»ØµÚÒ»´Î³öÏÖµÄλÖã¬Ã»Óзµ»Ø0
patindex('%pattern%', expression) ¹¦ÄÜͬÉÏ£¬¿ÉÊÇʹÓÃͨÅä·û
replicate('char', rep_time), Öظ´×Ö·û´®
reverse(char),µßµ¹×Ö·û´®
replace(str, strold, strnew) Ìæ»»×Ö·û´®
space(n), ²úÉún¸ö¿ÕÐÐ
stuff(), SELECT STUFF('abcdef', 2, 3, 'ijklmn') ='aijklmnef', 2ÊÇ¿ªÊ¼Î»Öã¬3ÊÇÒª´ÓÔÀ´´®ÖÐɾ³ýµÄ×Ö·û³¤¶È£¬ijlmnÊÇÒª²åÈëµÄ×Ö·û´®¡£
3£¬ÀàÐÍת»»º¯Êý:
cast, cast( expression as data_type), Example:
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales from titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
convert(data_type, expression)
4,ÈÕÆÚº¯Êý
day(), month(), year()
dateadd(datepart, number, date), datapartÖ¸¶¨¶ÔÄÇÒ»²¿·Ö¼Ó£¬numberÖªµÀ¼Ó¶àÉÙ£¬dateÖ¸¶¨ÔÚ˵Ļù´¡Éϼӡ£datepartµÄÈ¡Öµ°üÀ¨£¬year,quarter,month,dayofyear,day,week,hour,minute,second,±ÈÈçÃ÷Ìì dateadd(day,1, getdate())
datediff(datepart,date1,date2). datapartºÍÉÏÃæÒ»Ñù¡