SQL SERVER 函数举例

懂点IT的耿小厨 2020/5/8 23:05:47

需求说明将字符串按照指定的分隔符进行分割,并将结果按照从后往前的顺序倒序排列,拼接后的结果用‘’符连接。(也可以按照指定符号分割为多个列,修改最后一部分即可)创建测试表及数据创建一张测试表及数据yy

需求说明

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

创建测试表及数据

 /*  创建一张测试表及数据 */
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:

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

 

随时随地学软件编程-关注百度小程序和微信小程序
关于找一找教程网

本站文章仅代表作者观点,不代表本站立场,所有文章非营利性免费分享。
本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章,希望广大程序员努力学习,让我们用科技改变世界。
[SQL SERVER 函数举例]http://www.zyiz.net/tech/detail-133716.html

上一篇:在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)

下一篇:SQL SERVER 的窗体函数OVER的使用:row_number/rank/dense_rank

赞(0)

共有 条评论 网友评论

验证码: 看不清楚?
    关注微信小程序
    程序员编程王-随时随地学编程

    扫描二维码或查找【程序员编程王】

    可以随时随地学编程啦!

    技术文章导航 更多>
    扫一扫关注最新编程教程