MSSQL Split表字段代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 MSSQL Split表字段
作者: / 发布于2010/12/30/ 517
MSSQL Split表字段
<div>
<table>
    <tbody>
        <tr>
            <td class="content">"sql comments"><font color="#008200">--拆分字符串之后匹配结果集合 </font></td>
        </tr>
    </tbody>
</table>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">CREATE</font></strong> "sql keyword"><strong><font color="#006699">FUNCTION</font></strong> "sql plain">[dbo].[fnSplit]( </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">@sInputList "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(8000) "sql comments"><font color="#008200">-- List of delimited items </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">, @sDelimiter "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(8000) = "sql string"><font color="#0000ff">','</font> "sql comments"><font color="#008200">-- delimiter that separates items </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">) "sql keyword"><strong><font color="#006699">RETURNS</font></strong> "sql plain">@List "sql keyword"><strong><font color="#006699">TABLE</font></strong> "sql plain">(item "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(8000)) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">BEGIN</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">DECLARE</font></strong> "sql plain">@sItem "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(8000) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">BEGIN</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">SELECT</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">@sItem=RTRIM(LTRIM("sql color2"><font color="#ff1493">SUBSTRING</font>"sql plain">(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">@sInputList=RTRIM(LTRIM("sql color2"><font color="#ff1493">SUBSTRING</font>"sql plain">(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">IF LEN(@sItem) > 0 </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">INSERT</font></strong> "sql keyword"><strong><font color="#006699">INTO</font></strong> "sql plain">@List "sql keyword"><strong><font color="#006699">SELECT</font></strong> "sql plain">@sItem </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">END</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">IF LEN(@sInputList) > 0 </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">INSERT</font></strong> "sql keyword"><strong><font color="#006699">INTO</font></strong> "sql plain">@List "sql keyword"><strong><font color="#006699">SELECT</font></strong> "sql plain">@sInputList "sql comments"><font color="#008200">-- Put the last item in </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">RETURN</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">END</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql comments"><font color="#008200">----------------------使用方法 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">if object_id("sql string"><font color="#0000ff">'tempdb..#Tmp'</font>"sql plain">) "sql keyword"><strong><font color="#006699">is</font></strong> "sql color1"><font color="#808080">not</font> "sql color1"><font color="#808080">null</font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql keyword"><strong><font color="#006699">drop</font></strong> "sql keyword"><strong><font color="#006699">table</font></strong> "sql plain">#Tmp </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">create</font></strong> "sql keyword"><strong><font color="#006699">table</font></strong> "sql plain">#Tmp "sql comments"><font color="#008200">--创建临时表#Tmp </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">( </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces">"sql plain">ID "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(100) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">); </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">DECLARE</font></strong> "sql plain">@iid "sql keyword"><strong><font color="#006699">VARCHAR</font></strong>"sql plain">(100) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">declare</font></strong> "sql plain">@"sql keyword"><strong><font color="#006699">name</font></strong> "sql keyword"><strong><font color="#006699">varchar</font></strong>"sql plain">(500) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">declare</font></strong> "sql plain">cursor1 "sql keyword"><strong><font color="#006699">cursor</font></strong> "sql keyword"><strong><font color="#006699">for</font></strong> "sql comments"><font color="#008200">--定义游标cursor1 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">select</font></strong> "sql plain">iid,props "sql keyword"><strong><font color="#006699">from</font></strong> "sql plain">Iteminfos "sql comments"><font color="#008200">--使用游标的对象 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">open</font></strong> "sql plain">cursor1 "sql comments"><font color="#008200">--打开游标 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">fetch</font></strong> "sql keyword"><strong><font color="#006699">next</font></strong> "sql keyword"><strong><font color="#006699">from</font></strong> "sql plain">cursor1 "sql keyword"><strong><font color="#006699">into</font></strong> "sql plain">@iid,@"sql keyword"><strong><font color="#006699">name</font></strong> "sql comments"><font color="#008200">--将游标向下移1行，获取的数据放入之前定义的变量@iid,@name中 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">while @@fetch_status=0 "sql comments"><font color="#008200">--判断是否成功获取数据 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">begin</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql plain">IF(("sql keyword"><strong><font color="#006699">select</font></strong> "sql color2"><font color="#ff1493">COUNT</font>"sql plain">(*) "sql keyword"><strong><font color="#006699">FROM</font></strong> "sql plain">fnSplit(@"sql keyword"><strong><font color="#006699">name</font></strong>"sql plain">, "sql string"><font color="#0000ff">';'</font>"sql plain">) "sql keyword"><strong><font color="#006699">WHERE</font></strong> "sql plain">item = "sql string"><font color="#0000ff">'20000:20090'</font>"sql plain">)>0) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">INSERT</font></strong> "sql keyword"><strong><font color="#006699">INTO</font></strong> "sql plain">#Tmp (ID) "sql keyword"><strong><font color="#006699">VALUES</font></strong> "sql plain">(@iid) </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">fetch</font></strong> "sql keyword"><strong><font color="#006699">next</font></strong> "sql keyword"><strong><font color="#006699">from</font></strong> "sql plain">cursor1 "sql keyword"><strong><font color="#006699">into</font></strong> "sql plain">@iid,@"sql keyword"><strong><font color="#006699">name</font></strong> "sql comments"><font color="#008200">--将游标向下移1行 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">end</font></strong></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">close</font></strong> "sql plain">cursor1 "sql comments"><font color="#008200">--关闭游标 </font></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">deallocate</font></strong> "sql plain">cursor1 </td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt1">
<table>
    <tbody>
        <tr>
            <td class="content">"spaces"></td>
        </tr>
    </tbody>
</table>
</div>
<div class="line alt2">
<table>
    <tbody>
        <tr>
            <td class="content">"sql keyword"><strong><font color="#006699">SELECT</font></strong> "sql plain">* "sql keyword"><strong><font color="#006699">FROM</font></strong> "sql plain">dbo.Iteminfos "sql keyword"><strong><font color="#006699">WHERE</font></strong> "sql plain">iid "sql color1"><font color="#808080">IN</font>"sql plain">( "sql keyword"><strong><font color="#006699">SELECT</font></strong> "sql plain">ID "sql keyword"><strong><font color="#006699">FROM</font></strong> "sql plain">#Tmp)</td>
        </tr>
    </tbody>
</table>
</div>
</div>
试试其它关键字
　拆分　
同语言下
可能有用的
贡献的其它代码Label