{"id":1261,"date":"2012-06-04T22:35:27","date_gmt":"2012-06-04T21:35:27","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1261"},"modified":"2012-06-04T22:35:27","modified_gmt":"2012-06-04T21:35:27","slug":"sql-server-2012-ujdonsagok-7-uj-tsql-fuggvenyek","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2012\/06\/04\/sql-server-2012-ujdonsagok-7-uj-tsql-fuggvenyek\/","title":{"rendered":"SQL Server 2012 \u00fajdons\u00e1gok – 7. \u00daj TSQL f\u00fcggv\u00e9nyek"},"content":{"rendered":"

Sz\u00e1mos \u00faj f\u00fcggv\u00e9ny teszi egyszer\u0171bb\u00e9 a mindennapi TSQL programoz\u00e1st. A f\u00fcggv\u00e9nyek egy r\u00e9sze az Excel vagy az Access f\u00fcggv\u00e9nyeihez hasonl\u00f3, megk\u00f6nny\u00edtve az ezeken \u00edrt alkalmaz\u00e1sok \u00e1t\u00edr\u00e1s\u00e1t SQL Server 2012-re.
\nSz\u00e1mos \u00faj d\u00e1tumkezel\u0151 f\u00fcggv\u00e9ny\u00fcnk van. Az eomonth visszaadja egy adott d\u00e1tumhoz tartoz\u00f3 h\u00f3nap utols\u00f3 napj\u00e1t (kezelve sz\u00f6k\u0151\u00e9veket, stb.). A m\u00e1sodik param\u00e9ter\u00e9vel \u00e1t lehet t\u00e9rni egy m\u00e1sik h\u00f3napra is:<\/p>\n

\r\ndeclare @currentdate datetime = '20120225';\r\nselect \r\neomonth (@currentdate) as 'EOMMonth',\r\neomonth (@currentdate, -2) as 'EOM2MonthsEarlier',\r\neomonth (@currentdate, 1) as 'EOM1MonthLater';\r\n<\/pre>\n
\"\"<\/a><\/p>\n
D\u00e1tum \u00f6sszerak\u00e1sa a komponenseib\u0151l:<\/p>\n
\r\ndeclare @Year int = 2011;\r\ndeclare @Month int = 2;\r\ndeclare @Day int = 29;\r\n\r\nselect datefromparts(@Year, @Month, @Day) AS Result;\r\n<\/pre>\n
Hasonl\u00f3 f\u00fcggv\u00e9nyek k\u00e9sz\u00fcltek a t\u00f6bbi t\u00edpushoz is: datetimefromparts, datetime2fromparts, datetimeoffsetfromparts, smalldatetimefromparts, timefromparts.
\nA t\u00edpusok k\u00f6z\u00f6tti konverzi\u00f3 sokkal egyszer\u0171bb\u00e9 v\u00e1lt. Egyr\u00e9szt most m\u00e1r van try_convert, ami az eddigi convert f\u00fcggv\u00e9nnyel ellent\u00e9tben nem dob hib\u00e1t, ha nem siker\u00fcl a konverzi\u00f3, hanem nullt ad vissza:<\/p>\n
\r\nselect TRY_CONVERT(float, 'test') F\r\n, TRY_CONVERT(datetime2, '2012-05-15') D1\r\n, TRY_CONVERT(datetime2, '2012-05-15.') D2\r\n, TRY_CONVERT(datetime2, '2012-05-45') D3\r\n, TRY_CONVERT(datetime2, '25\/04\/2020', 103) D4;\t--British\/French: dd\/mm\/yyyy\r\n<\/pre>\n
\"\"<\/a><\/p>\n
A .NET-b\u0151l ismert alapt\u00edpusok Parse, TryParse \u00e9s ToString met\u00f3dus\u00e1t is kivezett\u00e9k, \u00edgy ezekkel gazdag, szab\u00e1lyozhat\u00f3 form\u00e1tum\u00fa \u00e9s kult\u00faraf\u00fcgg\u0151 \u00e9rtelmez\u00e9si \u00e9s form\u00e1z\u00e1si lehet\u0151s\u00e9gek jelentek meg az SQL Serverben.<\/p>\n
\r\nSELECT PARSE('7\/17\/2011' AS DATE USING 'en-US') AS dt1,\r\nPARSE('2011.07.17.' AS DATE USING 'hu-HU') AS dt2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
Az en-US angol nyelv, USA-ban \u00e9rtelmezve, a hu-HU magyar nyelv, Magyarorsz\u00e1gon \u00e9rtelmezve.
\nA format f\u00fcggv\u00e9ny seg\u00edts\u00e9g\u00e9vel a .NET-es t\u00edpusok ToStringj\u00e9t h\u00edvhatjuk meg, amivel rendk\u00edv\u00fcl egyszer\u0171 \u00f6sszetett form\u00e1z\u00e1sokat is megval\u00f3s\u00edtani:<\/p>\n
\r\ndeclare @Price decimal(19,4) = 13542.1251;\r\ndeclare @Date datetime2(0) = '20120425 15:13:00';\r\nselect \r\n\t--P\u00e9nz\u00f6sszegek\r\n\tformat(@Price, 'C4', 'hu-hu') HunPrice, --13 542,1251 Ft\r\n\tformat(@Price, 'C4', 'en-us') UsPrice,  --$13,542.1251\t\r\n\t--D\u00e1tumok\r\n\tformat(@Date, 'D', 'hu-hu') HunDate,    --2012. \u00e1prilis 25.\r\n\tformat(@Date, 'D', 'en-us') UsDate,     --Wednesday, April 25, 2012 \r\n\tformat(@Date, 'd', 'hu-hu') HunDate2,\r\n\tformat(@Date, 'd', 'en-us') UsDate2,\r\n\tformat(@Date, 'f', 'hu-hu') HunDate3,\r\n\tformat(@Date, 'f', 'en-us') UsDate3,\r\n\t--Custom format string\r\n\tformat(@Date, 'yyyy.MM.dd') HuncutDate3,\r\n\t--Sz\u00e1mok form\u00e1z\u00e1sa\r\n\tformat(@Price, 'F2', 'hu-hu') HunNumber,\r\n\tformat(@Price, 'F2', 'en-us') UsNumber,\r\n\tformat(@Price, '00000', 'en-us') LeadingZerosNumber,\r\n\tformat(@Price, 'N2', 'hu-hu') HunGroupedNumber,\r\n\tformat(@Price, 'N2', 'en-us') UsGroupedNumber;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
Az eddig case-ekkel megval\u00f3s\u00edtott logikai kifejez\u00e9sek egy r\u00e9sz\u00e9t egyszer\u0171bben is meg tudjuk fogalmazni az iif \u00e9s a choose f\u00fcggv\u00e9nyekkel:<\/p>\n
\r\nselect \r\na = iif(1=1, 'cica', 'kutya'),\t--cica\r\nb = iif(1=2, 'cica', 'kutya'),      --kutya\r\nn = iif(null = 1, 'cica', 'kutya'); --kutya\r\n<\/pre>\n
\"\"<\/a><\/p>\n
A choose az n. argumentumot adja vissza, 1-gyel kezd\u0151d\u0151 sorsz\u00e1mmal:<\/p>\n
\r\nselect \r\nchoose(1, 'a', 'b', 'c') as 'first', \r\nchoose(2, 'a', 'b', 'c') as 'second';\r\n<\/pre>\n
\"\"<\/a><\/p>\n
A concat f\u00fcggv\u00e9nnyel sz\u00f6vegeket lehet egybef\u0171zni. Abban k\u00fcl\u00f6nb\u00f6zik a + oper\u00e1tort\u00f3l, hogy mivel a param\u00e9terei stringek, ha m\u00e1s t\u00edpus megy be param\u00e9ter\u00fcl, az automatikusan stringg\u00e9 konvert\u00e1l\u00f3dik, \u00edgy biztosan stringk\u00e9nt ker\u00fcl \u00f6sszef\u0171z\u00e9sre . A m\u00e1sik k\u00fcl\u00f6nbs\u00e9g, hogy a NULL param\u00e9terek helyett \u00fcres stringet helyettes\u00edt be, m\u00edg a + oper\u00e1tor alap set opci\u00f3k eset\u00e9n NULL-t ad vissza.<\/p>\n
\r\ndeclare @PurchaseOrderNumber int = 123;\r\nselect concat('PO', @PurchaseOrderNumber) PO1, \r\nconcat('PO', NULL, @PurchaseOrderNumber) PO2;\r\n<\/pre>\n
\"\"<\/a><\/p>\n
B\u00e1r az adatok form\u00e1z\u00e1sa \u00e1ltal\u00e1ban nem az adatb\u00e1zis, hanem a megjelen\u00edt\u00e9si r\u00e9teg feladata, ennek ellen\u00e9re sokszor kellett eddig is p\u00e9ld\u00e1ul sz\u00e1mokat vagy d\u00e1tumokat form\u00e1zni SQL-ben is. Az \u00faj f\u00fcggv\u00e9nyekkel a .NET form\u00e1z\u00e1sok teljes reperto\u00e1rja el\u00e9rhet\u0151 az SQL programoz\u00f3k r\u00e9sz\u00e9re is.<\/p>\n","protected":false},"excerpt":{"rendered":"
Sz\u00e1mos \u00faj f\u00fcggv\u00e9ny teszi egyszer\u0171bb\u00e9 a mindennapi TSQL programoz\u00e1st. A f\u00fcggv\u00e9nyek egy r\u00e9sze az Excel vagy az Access f\u00fcggv\u00e9nyeihez hasonl\u00f3, megk\u00f6nny\u00edtve az…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,77],"tags":[],"class_list":["post-1261","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008-r2"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1261"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1261\/revisions"}],"predecessor-version":[{"id":1271,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1261\/revisions\/1271"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}