{"id":474,"date":"2017-03-06T16:45:59","date_gmt":"2017-03-06T07:45:59","guid":{"rendered":"http:\/\/kei0310.info\/?p=474"},"modified":"2017-03-06T16:50:41","modified_gmt":"2017-03-06T07:50:41","slug":"post-474","status":"publish","type":"post","link":"http:\/\/kei0310.info\/?p=474","title":{"rendered":"[SQLServer] SQLServer\u3067Split\u3092\u5b9f\u88c5\uff01"},"content":{"rendered":"<p>SQLServer\u3067Split\u3092\u5b9f\u88c5\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<p>\u6700\u65b0\u306eSQLServer\u306b\u306f\u7d44\u307f\u8fbc\u307f\u95a2\u6570\u304c\u3042\u308b\u3089\u3057\u3044\u3067\u3059\u304c\u3001<\/p>\n<p>\u308f\u305f\u3057\u306e\u4f7f\u3063\u3066\u3044\u308b\u30d0\u30fc\u30b8\u30e7\u30f3\u306b\u306f\u306a\u3044\uff01\u3067\u3059\u3002<\/p>\n<p>\u5143\u3005\u3001IP\u30a2\u30c9\u30ec\u30b9\u3092Split\u3057\u305f\u3044\u6848\u4ef6\u304c\u3042\u308a\u3001\u305d\u306e\u904e\u7a0b\u3067\u4f5c\u6210\u3057\u305f\u3082\u306e\u3067\u3059\u3002<\/p>\n<p><a title=\"http:\/\/stackoverflow.com\/questions\/10581772\/how-to-split-a-comma-separated-value-to-columns\" href=\"http:\/\/stackoverflow.com\/questions\/10581772\/how-to-split-a-comma-separated-value-to-columns\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/10581772\/how-to-split-a-comma-separated-value-to-columns<\/a><\/p>\n<p>\u3053\u3061\u3089\u3092\u4e38\u30d1\u30af\u30ea\u3057\u3066\u3044\u307e\u3059\u304c\u3001\u30d0\u30b0\u3063\u3066\u3044\u305f\u306e\u3067\u4fee\u6b63\u3002\uff08\u30b3\u30e1\u30f3\u30c8\u30a2\u30a6\u30c8\u306e\u3068\u3053\u308d\uff09<\/p>\n<p>\u203bIP\u30a2\u30c9\u30ec\u30b9\u4e2d\u306b\u540c\u3058\u201d\u6570\u5024\uff0bDelimiter\u201d\u304c\uff12\u3064\u542b\u307e\u308c\u3066\u3044\u308b\u3068\u3001Replace\u3067\u4e8c\u3064\u5909\u63db\u3055\u308c\u3066\u30d0\u30b0\u308b\u3002<\/p>\n<p>IP\u30a2\u30c9\u30ec\u30b9\u4ee5\u5916\u3092\u6271\u3046\u3068\u304d\u306f\u3001Delimiter\u306e'.'\u3092\u5225\u306a\u306e\u306b\u5dee\u3057\u66ff\u3048\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<h6>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\nCREATE FUNCTION [dbo].[SplitIP]\r\n(\r\n  @multiwordstring VARCHAR(255),\r\n  @wordnumber      NUMERIC\r\n)\r\nreturns VARCHAR(255)\r\nAS\r\n  BEGIN\r\n      DECLARE @remainingstring VARCHAR(255)\r\n      SET @remainingstring=@multiwordstring\r\n\r\n      DECLARE @numberofwords NUMERIC\r\n      SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '.', '')) + 1)\r\n\r\n      DECLARE @word VARCHAR(50)\r\n      DECLARE @parsedwords TABLE\r\n      (\r\n         line NUMERIC IDENTITY(1, 1),\r\n         word VARCHAR(255)\r\n      )\r\n\r\n      WHILE @numberofwords &gt; 1\r\n        BEGIN\r\n            SET @word=LEFT(@remainingstring, CHARINDEX('.', @remainingstring) - 1)\r\n\r\n            INSERT INTO @parsedwords(word)\r\n            SELECT @word\r\n\r\n--            SET @remainingstring= REPLACE(@remainingstring, Concat(@word, '.'), '')\r\n\t\t\tSET @remainingstring=substring(@remainingstring, CHARINDEX('.', @remainingstring)+1, len(@remainingstring))\r\n            SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '.', '')) + 1)\r\n\r\n            IF @numberofwords = 1\r\n              BREAK\r\n\r\n            ELSE\r\n              CONTINUE\r\n        END\r\n\r\n      IF @numberofwords = 1\r\n        SELECT @word = @remainingstring\r\n      INSERT INTO @parsedwords(word)\r\n      SELECT @word\r\n\r\n      RETURN\r\n        (SELECT word\r\n         FROM   @parsedwords\r\n         WHERE  line = @wordnumber)\r\n\r\n  END;\r\nGO\r\n<\/pre>\n<\/h6>\n","protected":false},"excerpt":{"rendered":"<p>SQLServer\u3067Split\u3092\u5b9f\u88c5\u3057\u3066\u307f\u307e\u3059\u3002 \u6700\u65b0\u306eSQLServer\u306b\u306f\u7d44\u307f\u8fbc\u307f\u95a2\u6570\u304c\u3042\u308b\u3089\u3057\u3044\u3067\u3059\u304c\u3001 \u308f\u305f\u3057\u306e\u4f7f\u3063\u3066\u3044\u308b\u30d0\u30fc\u30b8\u30e7\u30f3\u306b\u306f\u306a\u3044\uff01\u3067\u3059\u3002 \u5143\u3005\u3001IP\u30a2\u30c9\u30ec\u30b9\u3092Split\u3057\u305f\u3044\u6848\u4ef6\u304c\u3042\u308a\u3001\u305d\u306e\u904e\u7a0b\u3067\u4f5c\u6210 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[37,10],"tags":[],"_links":{"self":[{"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/posts\/474"}],"collection":[{"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/kei0310.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=474"}],"version-history":[{"count":4,"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/posts\/474\/revisions"}],"predecessor-version":[{"id":478,"href":"http:\/\/kei0310.info\/index.php?rest_route=\/wp\/v2\/posts\/474\/revisions\/478"}],"wp:attachment":[{"href":"http:\/\/kei0310.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/kei0310.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=474"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/kei0310.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}