SQLSERVER common functions, summary of stored procedures

function

1. Character splitting function

Add FUNCTION [dbo].[dnt_split]
(
 @splitstring varchar(max),
 @separator CHAR(1) = ', '
)
RETURNS @splitstringstable TABLE
(
[Id] int identity(1.1),Value] VARCHAR(max)
)
AS
BEGIN
    DECLARE @currentindex INT
    DECLARE @nextindex INT
    DECLARE @returntext VARCHAR(max)

    SELECT @currentindex=1

    WHILE(@currentindex< =datalength(@splitstring))
    BEGIN
        SELECT @nextindex=charindex(@separator.@splitstring.@currentindex)Separator returns 0 if @separator returns the first found separator after the @currentIndex table is queried
        IF(@nextindex=0 OR @nextindex IS NULL)
            SELECT @nextindex=datalength(@splitstring)+1
        
        SELECT @returntext=substring(@splitstring.@currentindex.@nextindex-@currentindex)

        INSERT INTO @splitstringstable([Value])
        VALUES(@returntext)
        
        SELECT @currentindex=@nextindex+1
    END
    RETURN
END
Copy the code

Input arguments such as select * from dnt_split(‘str1,str2′,’,’); The reverse reference is as follows ↓

2. Uppercase function for the first letter

Add function [dbo].[CF_GetPinYin](@str varchar(100))
returns varchar(8000)
as
begin
 declare @re varchar(8000),@crs varchar(10)
 declare @strlen int 
 select @strlen=len(@str),@re=' '
 while @strlen>0
 begin  
  set @crs= substring(@str.@strlen.1)
      select @re=
        case
        when @crs<'matter' then @crs
        when @crs< ='厑' then 'a'
        when @crs< ='Ai' then 'ai'
        when @crs< ='dark' then 'an'
        when @crs< ='Ang' then 'ang'
        when @crs< ='Ao' then 'ao'
        when @crs< ='欛' then 'ba'
        when @crs< ='瓸' then 'bai'
        when @crs< ='disc' then 'ban'
        when @crs< ='pounds' then 'bang'
        when @crs< ='鑤' then 'bao'
        when @crs< ='Bei' then 'bei'
        when @crs< ='輽' then 'ben'
        when @crs< ='penny' then 'beng'
        when @crs< ='鼊' then 'bi'
        when @crs< ='change' then 'bian'
        when @crs< ='maw' then 'biao'
        when @crs< ='don't' then 'bie'
        when @crs< ='rest' then 'bin'
        when @crs< ='靐' then 'bing'
        when @crs< ='terrorist' then 'bo'
        when @crs< ='book' then 'bu'
        when @crs< ='ð then 'ca'
        when @crs< ='乲' then 'cai'
        when @crs< ='爘' then 'can'
        when @crs< ='賶' then 'cang'
        when @crs< ='鼜' then 'cao'
        when @crs< ='簎' then 'ce'
        when @crs< ='笒' then 'cen'
        when @crs< ='乽' then 'ceng'
        when @crs< =The '-' then 'cha'
        when @crs< ='囆' then 'chai'
        when @crs< ='shock' then 'chan'
        when @crs< ='Chang' then 'chang'
        when @crs< ='觘' then 'chao'
        when @crs< ='爡' then 'che'
        when @crs< ='more' then 'chen'
        when @crs< ='scale' then 'cheng'
        when @crs< ='鷘' then 'chi'
        when @crs< ='spear' then 'chong'
        when @crs< ='Chou' then 'chou'
        when @crs< ='sit' then 'chu'
        when @crs< ='kick' then 'chuai'
        when @crs< ='鶨' then 'chuan'
        when @crs< ='mean' then 'chuang'
        when @crs< ='顀' then 'chui'
        when @crs< ='stupid' then 'chun'
        when @crs< ='縒' then 'chuo'
        when @crs< ='嗭' then 'ci'
        when @crs< ='謥' then 'cong'
        when @crs< ='central' then 'cou'
        when @crs< ='ð « then 'cu'
        when @crs< ='Cuan' then 'cuan'
        when @crs< ='臎' then 'cui'
        when @crs< ='籿' then 'cun'
        when @crs< ='wrong' then 'cuo'
        when @crs< ='橽' then 'da'
        when @crs< ='Dai' then 'dai'
        when @crs< ='饏' then 'dan'
        when @crs< ='闣' then 'dang'
        when @crs< ='standard' then 'dao'
        when @crs< ='the' then 'de'
        when @crs< ='Den' then 'den'
        when @crs< ='stirrup' then 'deng'
        when @crs< ='螮' then 'di'
        when @crs< ='dia' then 'dia'
        when @crs< ='驔' then 'dian'
        when @crs< ='鑃' then 'diao'
        when @crs< ='嚸' then 'die'
        when @crs< ='顁' then 'ding'
        when @crs< ='thulium' then 'diu'
        when @crs< ='霘' then 'dong'
        when @crs< ='鬭' then 'dou'
        when @crs< ='between' then 'du'
        when @crs< ='叾' then 'duan'
        when @crs< ='譵' then 'dui'
        when @crs< ='踲' then 'dun'
        when @crs< ='鵽' then 'duo'
        when @crs< ='an' then 'e'
        when @crs< ='press' then 'en'
        when @crs< ='Eng' then 'eng'
        when @crs< ='Er' then 'er'
        when @crs< ='send' then 'fa'
        when @crs< ='瀪' then 'fan'
        when @crs< ='let' then 'fang'
        when @crs< ='靅' then 'fei'
        when @crs< ='manta rays' then 'fen'
        when @crs< ='覅' then 'feng'
        when @crs< ='梻' then 'fo'
        when @crs< ='ð « then 'fou'
        when @crs< ='猤' then 'fu'
        when @crs< ='魀' then 'ga'
        when @crs< ='瓂' then 'gai'
        when @crs< ='灨' then 'gan'
        when @crs< ='now' then 'gang'
        when @crs< ='zirconium' then 'gao'
        when @crs< ='獦' then 'ge'
        when @crs< ='to' then 'gei'
        when @crs< ='搄' then 'gen'
        when @crs< ='Geng' then 'geng'
        when @crs< ='兣' then 'gong'
        when @crs< ='buy' then 'gou'
        when @crs< ='gu' then 'gu'
        when @crs< ='Gua' then 'gua'
        when @crs< ='恠' then 'guai'
        when @crs< ='鱹' then 'guan'
        when @crs< ='classic' then 'guang'
        when @crs< ='Gui' then 'gui'
        when @crs< ='謴' then 'gun'
        when @crs< ='腂' then 'guo'
        when @crs< ='ah' then 'ha'
        when @crs< ='饚' then 'hai'
        when @crs< ='鶾' then 'han'
        when @crs< ='kang hang' then 'hang'
        when @crs< ='兞' then 'hao'
        when @crs< ='靏' then 'he'
        when @crs< ='嬒' then 'hei'
        when @crs< ='hate' then 'hen'
        when @crs< ='堼' then 'heng'
        when @crs< ='to' then 'hong'
        when @crs< ='horseshoe crabs' then 'hou'
        when @crs< ='petrels' then 'hu'
        when @crs< ='蘳' then 'hua'
        when @crs< ='蘾' then 'huai'
        when @crs< ='鰀' then 'huan'
        when @crs< ='鎤' then 'huang'
        when @crs< ='顪' then 'hui'
        when @crs< ='Hun' then 'hun'
        when @crs< ='夻' then 'huo'
        when @crs< ='types' then 'ji'
        when @crs< ='gallon' then 'jia'
        when @crs< ='鑳' then 'jian'
        when @crs< ='謽' then 'jiang'
        when @crs< ='Jiao' then 'jiao'
        when @crs< ='繲' then 'jie'
        when @crs< ='齽' then 'jin'
        when @crs< ='竸' then 'jing'
        when @crs< ='蘔' then 'jiong'
        when @crs< ='ð £ then 'jiu'
        when @crs< ='爠' then 'ju'
        when @crs< ='羂' then 'juan'
        when @crs< ='Jue' then 'jue'
        when @crs< ='攈' then 'jun'
        when @crs< ='californium' then 'ka'
        when @crs< ='乫' then 'kai'
        when @crs< ='矙' then 'kan'
        when @crs< ='something' then 'kang'
        when @crs< ='鯌' then 'kao'
        when @crs< ='Ke' then 'ke'
        when @crs< ='褃' then 'ken'
        when @crs< ='his' then 'keng'
        when @crs< ='廤' then 'kong'
        when @crs< ='Kou' then 'kou'
        when @crs< ='emperor ku' then 'ku'
        when @crs< ='骻' then 'kua'
        when @crs< ='Kuai' then 'kuai'
        when @crs< ='Kuan' then 'kuan'
        when @crs< ='鑛' then 'kuang'
        when @crs< ='鑎' then 'kui'
        when @crs< ='trapped' then 'kun'
        when @crs< ='穒' then 'kuo'
        when @crs< ='La' then 'la'
        when @crs< ='lai' then 'lai'
        when @crs< ='糷' then 'lan'
        when @crs< ='唥' then 'lang'
        when @crs< ='軂' then 'lao'
        when @crs< ='Ge' then 'le'
        when @crs< ='脷' then 'lei'
        when @crs< ='Leng' then 'leng'
        when @crs< ='瓈' then 'li'
        when @crs< ='both' then 'lia'
        when @crs< ='纞' then 'lian'
        when @crs< ='ð ¨ then 'liang'
        when @crs< ='about' then 'liao'
        when @crs< ='ð « then 'lie'
        when @crs< ='Lin' then 'lin'
        when @crs< ='炩' then 'ling'
        when @crs< ='咯' then 'liu'
        when @crs< ='ð « then 'long'
        when @crs< ='after' then 'lou'
        when @crs< ='Lu' then 'lu'
        when @crs< ='Lv' then 'lv'
        when @crs< ='about' then 'luan'
        when @crs< ='擽' then 'lue'
        when @crs< ='theory' then 'lun'
        when @crs< ='鱳' then 'luo'
        when @crs< ='it' then 'ma'
        when @crs< ='霢' then 'mai'
        when @crs< ='蘰' then 'man'
        when @crs< ='蠎' then 'mang'
        when @crs< ='唜' then 'mao'
        when @crs< ='癦' then 'me'
        when @crs< ='Me' then 'mei'
        when @crs< =' ' ' then 'men'
        when @crs< ='霥' then 'meng'
        when @crs< ='羃' then 'mi'
        when @crs< ='surface' then 'mian'
        when @crs< ='the temple' then 'miao'
        when @crs< ='鱴' then 'mie'
        when @crs< ='SLATE cod croaker' then 'min'
        when @crs< ='詺' then 'ming'
        when @crs< ='right' then 'miu'
        when @crs< ='Mo' then 'mo'
        when @crs< ='Mou' then 'mou'
        when @crs< ='旀' then 'mu'
        when @crs< ='魶' then 'na'
        when @crs< ='錼' then 'nai'
        when @crs< ='婻' then 'nan'
        when @crs< ='Nang' then 'nang'
        when @crs< ='Er' then 'nao'
        when @crs< ='it' then 'ne'
        when @crs< ='焾' then 'nei'
        when @crs< ='tender' then 'nen'
        when @crs< ='can' then 'neng'
        when @crs< ='嬺' then 'ni'
        when @crs< ='艌' then 'nian'
        when @crs< ='wine' then 'niang'
        when @crs< ='urea' then 'niao'
        when @crs< ='钀' then 'nie'
        when @crs< ='拰' then 'nin'
        when @crs< ='Ning' then 'ning'
        when @crs< ='靵' then 'niu'
        when @crs< ='齈' then 'nong'
        when @crs< ='譳' then 'nou'
        when @crs< ='搙' then 'nu'
        when @crs< ='actions' then 'nv'
        when @crs< ='malaria' then 'nue'
        when @crs< ='燶' then 'nuan'
        when @crs< ='桛' then 'nuo'
        when @crs< ='鞰' then 'o'
        when @crs< ='retting' then 'ou'
        when @crs< ='袙' then 'pa'
        when @crs< ='磗' then 'pai'
        when @crs< ='鑻' then 'pan'
        when @crs< ='fat' then 'pang'
        when @crs< ='ð ª then 'pao'
        when @crs< ='height' then 'pei'
        when @crs< ='spouted' then 'pen'
        when @crs< ='喸' then 'peng'
        when @crs< ='鸊' then 'pi'
        when @crs< ='con' then 'pian'
        when @crs< ='Piao' then 'piao'
        when @crs< ='Pie' then 'pie'
        when @crs< ='hire' then 'pin'
        when @crs< ='apple' then 'ping'
        when @crs< ='intelligent' then 'po'
        when @crs< ='哛' then 'pou'
        when @crs< ='exposure' then 'pu'
        when @crs< ='蟿' then 'qi'
        when @crs< ='si' then 'qia'
        when @crs< ='fine' then 'qian'
        when @crs< ='- megawatt' then 'qiang'
        when @crs< ='Qiao' then 'qiao'
        when @crs< ='籡' then 'qie'
        when @crs< ='藽' then 'qin'
        when @crs< ='櫦' then 'qing'
        when @crs< ='瓗' then 'qiong'
        when @crs< ='regret' then 'qiu'
        when @crs< ='覻' then 'qu'
        when @crs< ='to' then 'quan'
        when @crs< ='礭' then 'que'
        when @crs< ='囕' then 'qun'
        when @crs< ='Ran' then 'ran'
        when @crs< ='let' then 'rang'
        when @crs< ='round' then 'rao'
        when @crs< ='hot' then 're'
        when @crs< ='餁' then 'ren'
        when @crs< ='陾' then 'reng'
        when @crs< ='Ri' then 'ri'
        when @crs< ='穃' then 'rong'
        when @crs< ='嶿' then 'rou'
        when @crs< ='dye' then 'ru'
        when @crs< ='礝' then 'ruan'
        when @crs< ='壡' then 'rui'
        when @crs< ='橍' then 'run'
        when @crs< ='鶸' then 'ruo'
        when @crs< ='栍' then 'sa'
        when @crs< ='虄' then 'sai'
        when @crs< ='ð ¨ then 'san'
        when @crs< ='lost' then 'sang'
        when @crs< ='髞' then 'sao'
        when @crs< ='ð « then 'se'
        when @crs< ='Can' then 'sen'
        when @crs< ='縇' then 'seng'
        when @crs< ='instant' then 'sha'
        when @crs< ='sun' then 'shai'
        when @crs< ='eel' then 'shan'
        when @crs< ='attach' then 'shang'
        when @crs< ='Shao' then 'shao'
        when @crs< ='ð ª then 'she'
        when @crs< ='creepy-crawlies' then 'shen'
        when @crs< ='from the' then 'sheng'
        when @crs< ='瓧' then 'shi'
        when @crs< ='ð ¨ then 'shou'
        when @crs< ='虪' then 'shu'
        when @crs< ='誜' then 'shua'
        when @crs< ='卛' then 'shuai'
        when @crs< ='腨' then 'shuan'
        when @crs< ='灀' then 'shuang'
        when @crs< ='sleep' then 'shui'
        when @crs< ='鬊' then 'shun'
        when @crs< ='treatments' then 'shuo'
        when @crs< ='乺' then 'si'
        when @crs< ='鎹' then 'song'
        when @crs< ='瘶' then 'sou'
        when @crs< ='Su' then 'su'
        when @crs< ='is' then 'suan'
        when @crs< ='鐩' then 'sui'
        when @crs< ='Sun' then 'sun'
        when @crs< ='蜶' then 'suo'
        when @crs< ='襨' then 'ta'
        when @crs< ='燤' then 'tai'
        when @crs< ='賧' then 'tan'
        when @crs< ='hot' then 'tang'
        when @crs< ='畓' then 'tao'
        when @crs< ='ð « then 'te'
        when @crs< ='朰' then 'teng'
        when @crs< ='Ti' then 'ti'
        when @crs< ='舚' then 'tian'
        when @crs< ='the one' then 'tiao'
        when @crs< ='Tie' then 'tie'
        when @crs< ='乭' then 'ting'
        when @crs< ='憅' then 'tong'
        when @crs< ='through' then 'tou'
        when @crs< ='鵵' then 'tu'
        when @crs< ='褖' then 'tuan'
        when @crs< ='駾' then 'tui'
        when @crs< ='坉' then 'tun'
        when @crs< ='Tuo' then 'tuo'
        when @crs< ='韤' then 'wa'
        when @crs< ='顡' then 'wai'
        when @crs< ='贎' then 'wan'
        when @crs< ='want' then 'wang'
        when @crs< ='躛' then 'wei'
        when @crs< ='Wen' then 'wen'
        when @crs< ='Weng' then 'weng'
        when @crs< ='never' then 'wo'
        when @crs< =' ' ' then 'wu'
        when @crs< ='Xi' then 'xi'
        when @crs< ='鏬' then 'xia'
        when @crs< ='鼸' then 'xian'
        when @crs< ='鱌' then 'xiang'
        when @crs< ='Xiao' then 'xiao'
        when @crs< ='Xie' then 'xie'
        when @crs< ='trouble' then 'xin'
        when @crs< ='臖' then 'xing'
        when @crs< ='敻' then 'xiong'
        when @crs< ='齅' then 'xiu'
        when @crs< ='Xu' then 'xu'
        when @crs< ='贙' then 'xuan'
        when @crs< ='瀥' then 'xue'
        when @crs< ='鑂' then 'xun'
        when @crs< ='ð « then 'ya'
        when @crs< ='twinkling' then 'yan'
        when @crs< ='sample' then 'yang'
        when @crs< ='key' then 'yao'
        when @crs< ='岃' then 'ye'
        when @crs< ='齸' then 'yi'
        when @crs< ='檼' then 'yin'
        when @crs< ='譍' then 'ying'
        when @crs< ='oh' then 'yo'
        when @crs< ='Yong' then 'yong'
        when @crs< ='skunk' then 'you'
        when @crs< ='爩' then 'yu'
        when @crs< ='let' then 'yuan'
        when @crs< ='鸙' then 'yue'
        when @crs< ='blank' then 'yun'
        when @crs< ='雥' then 'za'
        when @crs< ='縡' then 'zai'
        when @crs< ='饡' then 'zan'
        when @crs< ='dirty' then 'zang'
        when @crs< ='kitchen' then 'zao'
        when @crs< ='稄' then 'ze'
        when @crs< ='鱡' then 'zei'
        when @crs< ='囎' then 'zen'
        when @crs< ='give' then 'zeng'
        when @crs< ='they' then 'zha'
        when @crs< ='Zhai' then 'zhai'
        when @crs< ='驏' then 'zhan'
        when @crs< ='瞕' then 'zhang'
        when @crs< ='羄' then 'zhao'
        when @crs< =' 'not upgrade your media player then 'zhe'
        when @crs< ='Dan' then 'zhen'
        when @crs< ='the' then 'zheng'
        when @crs< ='豒' then 'zhi'
        when @crs< ='ð « then 'zhong'
        when @crs< ='flash' then 'zhou'
        when @crs< ='cast' then 'zhu'
        when @crs< ='dog' then 'zhua'
        when @crs< ='Zhuai' then 'zhuai'
        when @crs< ='Zhuan' then 'zhuan'
        when @crs< ='戅' then 'zhuang'
        when @crs< ='鑆' then 'zhui'
        when @crs< ='稕' then 'zhun'
        when @crs< ='籱' then 'zhuo'
        when @crs< ='stain' then 'zi'
        when @crs< ='vertical' then 'zong'
        when @crs< ='媰' then 'zou'
        when @crs< ='ð « then 'zu'
        when @crs< ='his' then 'zuan'
        when @crs< ='欈' then 'zui'
        when @crs< ='銌' then 'zun'
        when @crs< ='master' then 'zuo'
        --else @crs end+' '+@re,@strlen=@strlen-1
        -- Remove the gap between pinyin
        else  @crs end+' '+@re.@strlen=@strlen- 1 
   end
 return(@re)
end
Copy the code

Example select Dbo.cf_getPinyin (‘ like ‘);

The stored procedure

1. Query statistics by day, month, and year

The system function spt_values is used to add and subtract the current time from consecutive numbers

select  convert(varchar(7),dateadd(mm,-number,getdate()),23) as month.convert(varchar(10),dateadd(dd,-number,getdate()),23) as day.convert(varchar(13),dateadd(hh,-number,getdate()),120) as Hour

frommaster.. spt_valueswhere type='p' and number > =0 and number< =5
Copy the code