关注小程序 找一找教程网-随时随地学编程

SqlServer教程

SQL SERVER 函数举例

需求说明

将字符串按照指定的分隔符进行分割,并将结果按照从后往前的顺序倒序排列,拼接后的结果用‘/’符连接。(也可以按照指定符号分割为多个列,修改最后一部分即可)

创建测试表及数据

 /*  创建一张测试表及数据 */
create table tmp(id int primary key ,name varchar(200));
insert into   tmp
select 1,'组长,班长,校长,委员长';
insert into   tmp
select 2 ,'连长,营长,师长,军长,司令';
insert into   tmp
select 3 ,'村长,镇长,区长,市长,厅长,省长,部长'; 

创建函数

  create  FUNCTION [dbo].[f_split]
(
@str NVARCHAR(500),
@delimiter NVARCHAR(20)
)RETURNS @table2 TABLE(val NVARCHAR(500))
AS
BEGIN 
    DECLARE @index INT,@startsplit INT,@id INT ,@maxid int,@id2 int
    declare @table table (id int,val nvarchar(50))
    SELECT @index=CHARINDEX(@delimiter,@str),@startsplit=1,@id=1
    WHILE @index>0
    BEGIN
        IF @id>1  
        BEGIN 
            SELECT @startsplit=@index+LEN(@delimiter) 
            SELECT @index=CHARINDEX(@delimiter,@str,@startsplit)
    </span><span style="color: #0000ff">END</span> 
    <span style="color: #0000ff">IF</span> <span style="color: #008000">@index</span><span style="color: #808080">&gt;</span><span style="color: #800000; font-weight: bold">0</span>  
        <span style="color: #0000ff">BEGIN</span> 
            <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> <span style="color: #008000">@table</span> <span style="color: #0000ff">VALUES</span> (<span style="color: #008000">@id</span>,<span style="color: #ff00ff">SUBSTRING</span>(<span style="color: #008000">@str</span>,<span style="color: #008000">@startsplit</span>,<span style="color: #008000">@index</span><span style="color: #808080">-</span><span style="color: #008000">@startsplit</span><span style="color: #000000">)) 
        </span><span style="color: #0000ff">END</span> 
    <span style="color: #0000ff">ELSE</span> 
    <span style="color: #0000ff">BEGIN</span>   
        <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> <span style="color: #008000">@table</span> <span style="color: #0000ff">VALUES</span> (<span style="color: #008000">@id</span>,<span style="color: #ff00ff">SUBSTRING</span>(<span style="color: #008000">@str</span>,<span style="color: #008000">@startsplit</span>,<span style="color: #ff00ff">LEN</span>(<span style="color: #008000">@str</span>)<span style="color: #808080">-</span><span style="color: #008000">@startsplit</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">))
    </span><span style="color: #0000ff">END</span> 
    <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@id</span><span style="color: #808080">=</span><span style="color: #008000">@id</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span>
<span style="color: #0000ff">END</span>
<span style="color: #0000ff">select</span> <span style="color: #008000">@id2</span><span style="color: #808080">=</span><span style="color: #ff00ff">Min</span>(id) <span style="color: #0000ff">from</span>  <span style="color: #008000">@table</span>
<span style="color: #0000ff">select</span> <span style="color: #008000">@maxid</span><span style="color: #808080">=</span><span style="color: #ff00ff">MAX</span>(id) <span style="color: #0000ff">from</span>  <span style="color: #008000">@table</span>
<span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> <span style="color: #008000">@table2</span> <span style="color: #0000ff">select</span> val <span style="color: #0000ff">from</span> <span style="color: #008000">@table</span> <span style="color: #0000ff">where</span> id<span style="color: #808080">=</span><span style="color: #008000">@maxid</span>
<span style="color: #0000ff">select</span> <span style="color: #008000">@maxid</span><span style="color: #808080">=</span> <span style="color: #008000">@maxid</span> <span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span>
<span style="color: #0000ff">while</span> <span style="color: #008000">@id2</span><span style="color: #808080">&lt;=</span><span style="color: #008000">@maxid</span>
<span style="color: #0000ff">begin</span>
<span style="color: #0000ff">update</span> <span style="color: #008000">@table2</span> <span style="color: #0000ff">set</span> val<span style="color: #808080">=</span>val<span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">/</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span>(<span style="color: #0000ff">select</span> val <span style="color: #0000ff">from</span> <span style="color: #008000">@table</span> <span style="color: #0000ff">where</span> id<span style="color: #808080">=</span><span style="color: #008000">@maxid</span><span style="color: #000000"> )
</span><span style="color: #0000ff">select</span> <span style="color: #008000">@maxid</span> <span style="color: #808080">=</span><span style="color: #008000">@maxid</span><span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span>

<span style="color: #0000ff">end</span>

<span style="color: #0000ff">RETURN</span> 

END 

执行查询

 select  name,(select val from  [f_split](name,',')) 
from  tmp; 

结果如下

 

 

 TIPs:

以上函数包含字符串截取函数、字符串长度、字符串位置等,如需获取该函数的每步说明可以联系我获取,谢谢。