sql server中将一个字段根据某个字符拆分成多个字段显示

sql server 数据库中某张表（Person）的数据信息是：

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

介绍两种方案：

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

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

SELECT Address, PARSENAME(REPLACE([Address],'-','.'),4) as 小区名,--如果字段的内容是 4单元-12幢-203 那么此时小区名字段的信息就是NULLPARSENAME(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))ASBEGIN   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 然后返回ELSESUBSTRING(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') --结果是 0SELECT CHARINDEX('/',NULL) --结果是 NULL

 

执行之后的结果是：

ID	Birth
1	1900-02-12
2	1898-02-03
3
4	NULL

原标题：sql server中将一个字段根据某个字符拆分成多个字段显示

关键词：sql