• sql server 拆分字符


    sql server 数据库中某张表(Person)的数据信息是:

    ID

    Address

    1 平山花园-4单元-12幢-203
    2 香山花园-3单元-22幢-304

    现在有需求是,将地址信息显示形式改成4列 ,即小区名,单元号,楼房号,房间号  分成4列进行显示

    ID 小区名 单元号 楼房号 房间号
    1 平山花园 4单元 12幢 203
    2 香山花园 3单元 22幢 304

    介绍两种方案:

    第一种:最简单的办法就是调用sql server中自带函数PARSENAME来进行拆分,但是注意:最多只能拆分成4个字段。

    针对这个例子的实现:parsename默认是根据'.'进行拆分的,所以首先要做的是将字段中的‘-’替换成'.'

    SELECT Address, PARSENAME(REPLACE([Address],'-','.'),4) as 小区名,
    --如果字段的内容是 4单元-12幢-203 那么此时小区名字段的信息就是NULL
    PARSENAME(REPLACE([Address],'-','.'),3) as 单元号,
    PARSENAME(REPLACE([Address],'-','.'),2) as 楼房号,
    PARSENAME(REPLACE([Address],'-','.'),1) as 房间号 
    FROM Person

    PARSENAME详解:

    PARSENAME ( 'object_name' , object_piece ) 
    'object_name'

    要检索其指定部分的对象的名称。object_name 的数据类型为 sysname。此参数是可选的限定对象名称。如果对象名称的所有部分都是限定的,则此名称可包含四部分:服务器名称、数据库名称、所有者名称以及对象名称。

    object_piece

    要返回的对象部分。object_piece 的数据类型为 int 值,可以为下列值:

    1 = 对象名称

    2 = 架构名称

    3 = 数据库名称

    4 = 服务器名称

    PARSENAME 函数不指示指定名称的对象是否存在。PARSENAME 仅返回指定对象名称的指定部分。

    第二种方案:思路:写一个函数  根据特定的分割符号利用SUBSTRING和CHARINDEX将字段进行截取拆分,

    复制代码
    复制代码
    --这个函数  返回的是一个表
    
    ALTER FUNCTION [dbo].[f_splitlianxi]
    (
    @str NVARCHAR(500),--要分割的字符串
    @fengefu NVARCHAR(20)--进行分割的符号
    )RETURNS @table TABLE(id INT,val NVARCHAR(50))
    AS
    BEGIN 
        DECLARE @index INT,@startsplit INT,@id INT --@index分隔符所在的位置的下标 @startsplit 每次分割开始的位置
        SELECT @index=CHARINDEX(@fengefu,@str),@startsplit=1,@id=1
        WHILE @index>0
        BEGIN
            IF @id>1  --第一次循环之后 都执行这个方法体中的代码
            BEGIN 
                SELECT @startsplit=@index+LEN(@fengefu) --分割开始位置等于 之前的字符下标位置+字符本身的长度
                SELECT @index=CHARINDEX(@fengefu,@str,@startsplit)
                
            END 
            IF @index>0   --第一次循环 截取位置从@startsplit=1开始  
                BEGIN 
                    INSERT INTO @table VALUES (@id,SUBSTRING(@str,@startsplit,@index-@startsplit)) --要截取的字符串   开始位置  要截取的长度
                END 
            ELSE 
            BEGIN   --最后一次循环  此时由于匹配不到分割字符 @index=0 截取剩余的字符串
                INSERT INTO @table VALUES (@id,SUBSTRING(@str,@startsplit,LEN(@str)-@startsplit+1))
            END 
            SELECT @id=@id+1
        END
        RETURN 
    END
    复制代码
    复制代码

    调用规则:

    SELECT [Address],(SELECT val FROM [f_splitlianxi]([Address],'-') WHERE id=1 ) AS 小区名 ,
    (SELECT val FROM [f_splitlianxi]([Address],'-') WHERE id=2 )AS 单元号,
    (SELECT val FROM [f_splitlianxi]([Address],'-') WHERE id=3 )AS 楼房号,
    (SELECT val FROM [f_splitlianxi]([Address],'-') WHERE id=4 )AS 房间号
    FROM Person

    PS:还有一种更加粗暴的写法  就是不用另外创建函数 直接select 的时候  就通过截取字符串,来附加新的列

    例如:有一个需求是 T_Person表中有字段Birth  里面的信息在输入的时候  形式不符合规定

    ID Birth
    1 1900/2/12
    2 1898/2/3
    3  
    4 NULL

    现在要求统一形式成:1987-05-03的形式:

    下面是具体过程:
    复制代码
    复制代码
    UPDATE dbo.T_Person SET Birth=
    SUBSTRING(Birth,1,CHARINDEX('/',Birth)-1) --得到年份
    +'-'+
    CASE WHEN SUBSTRING(Birth,CHARINDEX('/',Birth)+1,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)-(CHARINDEX('/',Birth)+1))<10 
    --截取月份与10进行比较 
    THEN 
    '0'+SUBSTRING(Birth,CHARINDEX('/',Birth)+1,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)-(CHARINDEX('/',Birth)+1))
    --小于10的时候 在月份前面追加个0 然后返回
    ELSE 
    SUBSTRING(Birth,CHARINDEX('/',Birth)+1,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)-(CHARINDEX('/',Birth)+1))
    --大于10的时候 截取月份返回
    END
    --得到月份
    +'-'
    +CASE WHEN SUBSTRING(Birth,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)+1,LEN(Birth)-CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1))<10
    --截取日与10进行比较
    THEN 
    '0'+SUBSTRING(Birth,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)+1,LEN(Birth)-CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1))
    --小于10的时候 在日前面追加个0 然后返回
    ELSE
    SUBSTRING(Birth,CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1)+1,LEN(Birth)-CHARINDEX('/',Birth,CHARINDEX('/',Birth)+1))
    --大于10的时候 截取日返回
    END
    --得到日
    WHERE CHARINDEX('/',Birth)>0 --防止没有/的信息字段 在进行字符串截取的时候报错 没有'/'的返回0 但是NULL 得到的值是NULL
    
    --注释
    SELECT CHARINDEX('/','123') --结果是 0
    SELECT CHARINDEX('/',NULL) --结果是 NULL
    复制代码
    复制代码

    执行之后的结果是:

    ID Birth
    1 1900-02-12
    2 1898-02-03
    3  
    4 NULL
  • 相关阅读:
    Express本地测试HTTPS
    在 WebStorm 中,配置能够识别 Vue CLI 3 创建的项目的别名 alias
    在线版本的ps
    功能强大的任务日历组件
    tree-shaking实战
    深入diff 算法
    【题解】[SHOI2001] Panda 的烦恼
    【题解】[JLOI2011]不重复数字
    「Codeforces Global Round #10」赛后个人总结
    【题解】[SCOI2004] 文本的输入
  • 原文地址:https://www.cnblogs.com/z45281625/p/13384841.html
Copyright © 2020-2023  润新知