8.1 时间日期函数
select dateAdd(day, 3, getDate()),dateAdd(month,3, getDate());--加
select dateDiff(day, '2011-06-20', getDate());--相差天数
select dateName(month, getDate());--当前月份
select datePart(month, getDate());--当前月份
--把时间以指定的格式输出
Select convert(varchar(100), getdate(), 23)--2006-05-16
Select convert(varchar(100), getdate(), 111)--2006/05/16
Select convert(varchar(100), getdate(), 112)--20060516
Select convert(varchar(100), getdate(), 114)--10:57:49:547
Select convert(varchar(100), getdate(), 120)--2006-05-16 10:57:49
Select convert(varchar(100), getdate(), 121)--2006-05-16 10:57:49.700
Select convert(varchar(100), getdate(), 126)--2006-05-16T10:57:49.827
--提取特殊日期
select dateadd(dd,-day(getdate())+1,getdate())--本月第一天
select dateadd(mm,datediff(mm,0,getdate()),0) --本月第一天
select dateadd(dd,-day(getdate()),dateadd(m,1,getdate()))--本月最后一天
select getdate()+6-datepart(weekday,getdate())--本周星期五
select getdate()+5-datepart(weekday,getdate())--本周星期四
select getdate()+4-datepart(weekday,getdate())--本周星期三
select getdate()+3-datepart(weekday,getdate())--本周星期二
select getdate()+2-datepart(weekday,getdate())--本周星期一
select dateadd(wk,datediff(wk,0,getdate()),0)--本周的星期一
select dateadd(yy,datediff(yy,0,getdate()),0)--一年的第一天
select dateadd(qq,datediff(qq,0,getdate()),0)--季度的第一天
select dateadd(dd,datediff(dd,0,getdate()),0)--当天的半夜
select day(dateadd(dd,-1,dateadd(mm,1,Dateadd(mm,datediff(mm,0,getdate()),0))))--当前月份的最大天数
select getdate()+6-datepart(weekday,getdate())%7 --任意日期后的第一个星期五
--平年或者闰年
select caseday(dateadd(mm,2,dateadd(ms,-3,dateadd(yy,datediff(yy,0,getdate()),0))))
when 28 then'平年'else '闰年' end
select casedatediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))
when 28 then'平年'else '闰年' end
语法格式：
WAITFOR DELAY N'小时数:分钟数:秒数.毫秒数'
等待100毫秒：
SELECT GETDATE()
WAITFOR DELAY N'00:00:00.100'
SELECT GETDATE()
需要注意的是，WAITFOR DELAY后面等待的时间格式必须符合SQL Server的DATETIME时间格式的规范，例如毫秒数不能大于999，秒数不能大于59，分钟数不能大于59，小时数不能大于23。
8.2 字符串函数
select patindex( '%a' , 'ta' ), patindex( '%ac%' , 'jack' ), patindex( 'dex%' , 'dexjack' ); -- 匹配字符索引
select 'a'+ space ( 2 ) + 'b' , 'c' + space ( 5 ) + 'd' ; -- 输出空格
select charIndex( 'o' , 'hello world' ); -- 查找索引
select charIndex( 'o' , 'hello world' , 6 ); -- 查找索引
select quoteName( 'abc[]def' ), quoteName( '123]45' );
select difference( 'hello' , 'helloWorld' ); -- 比较字符串相同
select replace( 'abcedef' , 'e' , 'E' ); -- 替换字符串
select stuff( 'hello world' , 3 , 4 , 'ABC' ); -- 指定位置替换字符串
select replicate( 'abc#' , 3 ); -- 重复字符串
select subString( 'hello World' , 7 , 5 ); -- 截取字符串( 7 -- 起点，--长度)
select len( 'abc' ); -- 返回长度
select reverse( 'sqlServer' ); -- 反转字符串
select left( 'leftString' , 7 ), right ( 'leftString' , 6 ) -- 取右边字符串
select lower( 'aBc' ), lower ( 'ABC' ); -- 小写
select upper( 'aBc' ), upper ( 'abc' ); -- 大写
select rtrim(ltrim( ' abc ' )) -- 去掉空格
select replace( ' a b c as ' , ' ' , '' ); -- 去掉所有空格
select coalesce(null,2,3) --返回第一个不为空的值
select len( '辽宁沈阳' ),datalength( '辽宁沈阳' ) --7, 11
select CHAR(9) -- 制表符
select CHAR(10) --换行符
select CHAR(13) --回车符
8.3 简体和繁体转换
//创建字符编码表
create tablecodetable(gb nchar(1),big nchar(1))
//准本数据
select top1298 id=identity(int,1,1) into #t from syscolumns a,syscolumns b
declare @j nvarchar(2000),@f nvarchar(2000)
select @j='皑蔼碍爱翱袄奥坝罢摆败颁办绊帮绑镑谤剥饱宝报鲍辈贝钡狈备惫绷笔毕毙币闭边编贬变辩辫标鳖别瘪濒滨宾摈饼并拨钵铂驳卜补财参蚕残惭惨灿苍舱仓沧厕侧册测层诧搀掺蝉馋谗缠铲产阐颤场尝长偿肠厂畅钞车彻尘沉陈衬撑称惩诚骋痴迟驰耻齿炽冲虫宠畴踌筹绸丑橱厨锄雏础储触处传疮闯创锤纯绰辞词赐聪葱囱从丛凑蹿窜错达带贷担单郸掸胆惮诞弹当挡党荡档捣岛祷导盗灯邓敌涤递缔颠点垫电淀钓调迭谍叠钉顶锭订丢东动栋冻斗犊独读赌镀锻断缎兑队对吨顿钝夺堕鹅额讹恶饿儿尔饵贰发罚阀珐矾钒烦范贩饭访纺飞诽废费纷坟奋愤粪丰枫锋风疯冯缝讽凤肤辐抚辅赋复负讣妇缚该钙盖干赶秆赣冈刚钢纲岗皋镐搁鸽阁铬个给龚宫巩贡钩沟构购够蛊顾剐关观馆惯贯广规硅归龟闺轨诡柜贵刽辊滚锅国过骇韩汉号阂鹤贺横轰鸿红后壶护沪户哗华画划话怀坏欢环还缓换唤痪焕涣黄谎挥辉毁贿秽会烩汇讳诲绘荤浑伙获货祸击机积饥讥鸡绩缉极辑级挤几蓟剂济计记际继纪夹荚颊贾钾价驾歼监坚笺间艰缄茧检碱硷拣捡简俭减荐槛鉴践贱见键舰剑饯渐溅涧将浆蒋桨奖讲酱胶浇骄娇搅铰矫侥脚饺缴绞轿较秸阶节茎鲸惊经颈静镜径痉竞净纠厩旧驹举据锯惧剧鹃绢杰洁结诫届紧锦仅谨进晋烬尽劲荆觉决诀绝钧军骏开凯颗壳课垦恳抠库裤夸块侩宽矿旷况亏岿窥馈溃扩阔蜡腊莱来赖蓝栏拦篮阑兰澜谰揽览懒缆烂滥捞劳涝乐镭垒类泪篱离里鲤礼丽厉励砾历沥隶俩联莲连镰怜涟帘敛脸链恋炼练粮凉两辆谅疗辽镣猎临邻鳞凛赁龄铃凌灵岭领馏刘龙聋咙笼垄拢陇楼娄搂篓芦卢颅庐炉掳卤虏鲁赂禄录陆驴吕铝侣屡缕虑滤绿峦挛孪滦乱抡轮伦仑沦纶论萝罗逻锣箩骡骆络妈玛码蚂马骂吗买麦卖迈脉瞒馒蛮满谩猫锚铆贸么霉没镁门闷们锰梦谜弥觅幂绵缅庙灭悯闽鸣铭谬谋亩钠纳难挠脑恼闹馁内拟腻撵捻酿鸟聂啮镊镍柠狞宁拧泞钮纽脓浓农疟诺欧鸥殴呕沤盘庞赔喷鹏骗飘频贫苹凭评泼颇扑铺朴谱栖凄脐齐骑岂启气弃讫牵扦钎铅迁签谦钱钳潜浅谴堑枪呛墙蔷强抢锹桥乔侨翘窍窃钦亲寝轻氢倾顷请庆琼穷趋区躯驱龋颧权劝却鹊确让饶扰绕热韧认纫荣绒软锐闰润洒萨鳃赛叁伞丧骚扫涩杀纱筛晒删闪陕赡缮伤赏烧绍赊摄慑设绅审婶肾渗声绳胜圣师狮湿诗尸时蚀实识驶势适释饰视试寿兽枢输书赎属术树竖数帅双谁税顺说硕烁丝饲耸怂颂讼诵擞苏诉肃虽随绥岁孙损笋缩琐锁獭挞抬态摊贪瘫滩坛谭谈叹汤烫涛绦讨腾誊锑题体屉条贴铁厅听烃铜统头秃图涂团颓蜕脱鸵驮驼椭洼袜弯湾顽万网韦违围为潍维苇伟伪纬谓卫温闻纹稳问瓮挝蜗涡窝卧呜钨乌污诬无芜吴坞雾务误锡牺袭习铣戏细虾辖峡侠狭厦吓锨鲜纤咸贤衔闲显险现献县馅羡宪线厢镶乡详响项萧嚣销晓啸蝎协挟携胁谐写泻谢锌衅兴汹锈绣虚嘘须许叙绪续轩悬选癣绚学勋询寻驯训讯逊压鸦鸭哑亚讶阉烟盐严颜阎艳厌砚彦谚验鸯杨扬疡阳痒养样瑶摇尧遥窑谣药爷页业叶医铱颐遗仪彝蚁艺亿忆义诣议谊译异绎荫阴银饮隐樱婴鹰应缨莹萤营荧蝇赢颖哟拥佣痈踊咏涌优忧邮铀犹游诱舆鱼渔娱与屿语吁御狱誉预驭鸳渊辕园员圆缘远愿约跃钥岳粤悦阅云郧匀陨运蕴酝晕韵杂灾载攒暂赞赃脏凿枣灶责择则泽贼赠扎札轧铡闸栅诈斋债毡盏斩辗崭栈战绽张涨帐账胀赵蛰辙锗这贞针侦诊镇阵挣睁狰争帧郑证织职执纸挚掷帜质滞钟终种肿众诌轴皱昼骤猪诸诛烛瞩嘱贮铸筑驻专砖转赚桩庄装妆壮状锥赘坠缀谆着浊兹资渍踪综总纵邹诅组钻'
,@f='皚藹礙愛翺襖奧壩罷擺敗頒辦絆幫綁鎊謗剝飽寶報鮑輩貝鋇狽備憊繃筆畢斃幣閉邊編貶變辯辮標鼈別癟瀕濱賓擯餅並撥缽鉑駁蔔補財參蠶殘慚慘燦蒼艙倉滄廁側冊測層詫攙摻蟬饞讒纏鏟産闡顫場嘗長償腸廠暢鈔車徹塵沈陳襯撐稱懲誠騁癡遲馳恥齒熾沖蟲寵疇躊籌綢醜櫥廚鋤雛礎儲觸處傳瘡闖創錘純綽辭詞賜聰蔥囪從叢湊躥竄錯達帶貸擔單鄲撣膽憚誕彈當擋黨蕩檔搗島禱導盜燈鄧敵滌遞締顛點墊電澱釣調叠諜疊釘頂錠訂丟東動棟凍鬥犢獨讀賭鍍鍛斷緞兌隊對噸頓鈍奪墮鵝額訛惡餓兒爾餌貳發罰閥琺礬釩煩範販飯訪紡飛誹廢費紛墳奮憤糞豐楓鋒風瘋馮縫諷鳳膚輻撫輔賦複負訃婦縛該鈣蓋幹趕稈贛岡剛鋼綱崗臯鎬擱鴿閣鉻個給龔宮鞏貢鈎溝構購夠蠱顧剮關觀館慣貫廣規矽歸龜閨軌詭櫃貴劊輥滾鍋國過駭韓漢號閡鶴賀橫轟鴻紅後壺護滬戶嘩華畫劃話懷壞歡環還緩換喚瘓煥渙黃謊揮輝毀賄穢會燴彙諱誨繪葷渾夥獲貨禍擊機積饑譏雞績緝極輯級擠幾薊劑濟計記際繼紀夾莢頰賈鉀價駕殲監堅箋間艱緘繭檢堿鹼揀撿簡儉減薦檻鑒踐賤見鍵艦劍餞漸濺澗將漿蔣槳獎講醬膠澆驕嬌攪鉸矯僥腳餃繳絞轎較稭階節莖鯨驚經頸靜鏡徑痙競淨糾廄舊駒舉據鋸懼劇鵑絹傑潔結誡屆緊錦僅謹進晉燼盡勁荊覺決訣絕鈞軍駿開凱顆殼課墾懇摳庫褲誇塊儈寬礦曠況虧巋窺饋潰擴闊蠟臘萊來賴藍欄攔籃闌蘭瀾讕攬覽懶纜爛濫撈勞澇樂鐳壘類淚籬離裏鯉禮麗厲勵礫曆瀝隸倆聯蓮連鐮憐漣簾斂臉鏈戀煉練糧涼兩輛諒療遼鐐獵臨鄰鱗凜賃齡鈴淩靈嶺領餾劉龍聾嚨籠壟攏隴樓婁摟簍蘆盧顱廬爐擄鹵虜魯賂祿錄陸驢呂鋁侶屢縷慮濾綠巒攣孿灤亂掄輪倫侖淪綸論蘿羅邏鑼籮騾駱絡媽瑪碼螞馬罵嗎買麥賣邁脈瞞饅蠻滿謾貓錨鉚貿麽黴沒鎂門悶們錳夢謎彌覓冪綿緬廟滅憫閩鳴銘謬謀畝鈉納難撓腦惱鬧餒內擬膩攆撚釀鳥聶齧鑷鎳檸獰甯擰濘鈕紐膿濃農瘧諾歐鷗毆嘔漚盤龐賠噴鵬騙飄頻貧蘋憑評潑頗撲鋪樸譜棲淒臍齊騎豈啓氣棄訖牽扡釺鉛遷簽謙錢鉗潛淺譴塹槍嗆牆薔強搶鍬橋喬僑翹竅竊欽親寢輕氫傾頃請慶瓊窮趨區軀驅齲顴權勸卻鵲確讓饒擾繞熱韌認紉榮絨軟銳閏潤灑薩鰓賽三傘喪騷掃澀殺紗篩曬刪閃陝贍繕傷賞燒紹賒攝懾設紳審嬸腎滲聲繩勝聖師獅濕詩屍時蝕實識駛勢適釋飾視試壽獸樞輸書贖屬術樹豎數帥雙誰稅順說碩爍絲飼聳慫頌訟誦擻蘇訴肅雖隨綏歲孫損筍縮瑣鎖獺撻擡態攤貪癱灘壇譚談歎湯燙濤縧討騰謄銻題體屜條貼鐵廳聽烴銅統頭禿圖塗團頹蛻脫鴕馱駝橢窪襪彎灣頑萬網韋違圍爲濰維葦偉僞緯謂衛溫聞紋穩問甕撾蝸渦窩臥嗚鎢烏汙誣無蕪吳塢霧務誤錫犧襲習銑戲細蝦轄峽俠狹廈嚇鍁鮮纖鹹賢銜閑顯險現獻縣餡羨憲線廂鑲鄉詳響項蕭囂銷曉嘯蠍協挾攜脅諧寫瀉謝鋅釁興洶鏽繡虛噓須許敘緒續軒懸選癬絢學勳詢尋馴訓訊遜壓鴉鴨啞亞訝閹煙鹽嚴顔閻豔厭硯彥諺驗鴦楊揚瘍陽癢養樣瑤搖堯遙窯謠藥爺頁業葉醫銥頤遺儀彜蟻藝億憶義詣議誼譯異繹蔭陰銀飲隱櫻嬰鷹應纓瑩螢營熒蠅贏穎喲擁傭癰踴詠湧優憂郵鈾猶遊誘輿魚漁娛與嶼語籲禦獄譽預馭鴛淵轅園員圓緣遠願約躍鑰嶽粵悅閱雲鄖勻隕運蘊醞暈韻雜災載攢暫贊贓髒鑿棗竈責擇則澤賊贈紮劄軋鍘閘柵詐齋債氈盞斬輾嶄棧戰綻張漲帳賬脹趙蟄轍鍺這貞針偵診鎮陣掙睜猙爭幀鄭證織職執紙摯擲幟質滯鍾終種腫衆謅軸皺晝驟豬諸誅燭矚囑貯鑄築駐專磚轉賺樁莊裝妝壯狀錐贅墜綴諄著濁茲資漬蹤綜總縱鄒詛組鑽'
//插入数据
insert codetable
select substring(@j,id,1),substring(@f,id,1) from #t where id<=len(@j)
//创建字符编码函数
create functionf_GB2BIG(
@str nvarchar(4000), --要转换的字符串
@toBIG bit --转换标志,为,表示GB-->BIG,否则是BIG-->GB
)returns nvarchar(4000)
as
begin
if @toBIG=1
select @str=replace(@str,gb,big)
from codetable
where charindex(gb,@str)>0
else
select @str=replace(@str,big,gb)
from codetable
where charindex(big,@str)>0
return(@str)
end
go
/*--调用示例
--转换为繁体
select dbo.f_GB2BIG('我是中国人',1)
--转换为简体
select dbo.f_GB2BIG('我是中國人',0)
--*/
8.4 半角全角转换
CREATE FUNCTION ConvertWordAngle (
@str NVARCHAR(4000), --要转换的字符串
@flag bit--转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[！-～]%',@step=-65248, @str=REPLACE(@str,N'',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248, @str=REPLACE(@str,N' ',N'')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i> 0
SELECT @str=REPLACE(@str, SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)),
@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
8.5 获取汉字首字母
create functionfn_GetInitialLetter(@ChineseString nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @SingleCharacter nchar(1)
declare @ReturnString nvarchar(4000)
set @ReturnString = ''
while len(@ChineseString)>0
begin
--依次取单个字符
set @SingleCharacter = left(@ChineseString,1)
----汉字字符，返回字符对应首字母,非汉字字符,返回原字符
if(unicode(@SingleCharacter) between 19968 and 19968+20901)
begin
set @ReturnString =@ReturnString +
(
select TOP 1 PY from (
select 'A' as PY,N'骜' as ChineseCharacters
union all select 'B',N'簿'
union all select 'C',N'错'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鳆'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'沤'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'箨'
union all select 'W',N'鹜'
union all select 'X',N'鑂'
union all select 'Y',N'韵'
union all select 'Z',N'咗'
) SpellingTable
where ChineseCharacters >= @SingleCharacter COLLATEChinese_PRC_CS_AS_KS_WS
order by PY asc
)
end
else
begin
set @ReturnString =@ReturnString + @SingleCharacter
end
set @ChineseString = RIGHT(@ChineseString,LEN(@ChineseString)-1)
end
return @ReturnString
END
GO
SELECT dbo.fn_GetInitialLetter('中华人民共和国(1949 - 2016)')
8.6 获取字符串中的汉字
create function[dbo].[m_getchinese]
(
@chinese nvarchar(max)
)
returns varchar(100)
as
begin
while patindex('%[^吖-咗]%',@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
return @chinese
end
Go
select dbo.m_getchinese
8.7 获取字符串中的字母
create function [dbo].[m_getstr](@maco varchar(100))
returns varchar(max)
as
begin
while patindex('%[^a-z]%',@maco) > 0
begin
set @maco=stuff(@maco,patindex('%[^a-z]%',@maco),1,'')
end
return @maco
end
go
select dbo.m_getstr
8.8 获取字符串中的数字
create function[dbo].[m_getnumber]
(
@mysql_one nvarchar(200)
)
returns varchar(200)
begin
declare @mysql_two varchar(200)
select @mysql_two=
substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),patindex('%[^0-9.]%',substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),
len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))-1)
return @mysql_two;
End
--测试
select dbo.m_getnumber
8.9 去除小数点后面的0
CREATE function[dbo].ClearZero
returns varchar(50)
as
begin
declare @returnValue varchar(20)
if(@inValue='')
set @returnValue='' --空的时候为空
else if(charindex('.',@inValue) ='0')
set @returnValue=@inValue--针对不含小数点的
else if( substring(reverse(@inValue),patindex('%[^0]%',reverse(@inValue)),1)='.')
set @returnValue =left(@inValue,len(@inValue)-patindex('%[^0]%',reverse(@inValue))) --针对小数点后全是的
else
set @returnValue =left(@inValue,len(@inValue)- patindex('%[^0]%.%',reverse(@inValue))+1) --其他任何情形
return @returnValue
end
8.10 去除字符串中所有的符号(逗号除外)
CREATE FUNCTION[dbo].[deal_all_sign] (@svarchar ( 200 ))
RETURNS varchar( 200 )
AS
BEGIN
declare @i int
while patindex( '%[^0-9,^a-z,^A-Z,^吖座]%' ,@s) > 0
begin
set @i = patindex( '%[^0-9,^a-z,^A-Z,^吖座]%',@s)
Set @s = stuff(@s,@i, 1 , '' )
end
return @s
END
8.11 去除HTML文件中的标签
CREATE FUNCTION[dbo].[removehtml] (@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
WHILE CHARINDEX('<', @str) > 0
BEGIN
SET @str = STUFF(@str, CHARINDEX('<', @str), CHARINDEX('>', @str) - CHARINDEX('<', @str) + 1, '');
end
RETURN @str
END;
十进制转二进制
create function f_int2bin(@i int)
returns varchar(31)
as
begin
declare @s varchar(31);
set @s=''
while (@i>0)
select @s=cast(@i%2 as varchar)+@s, @i=@i/2
return(@s)
end
go
--调用
select dbo.f_int2bin(64)
