sqlserver中将⼀个字段根据某个字符拆分成多个字段显⽰
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
11900/2/12
21898/2/3
3
4NULL
现在要求统⼀形式成：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
11900-02-12
21898-02-03
3
4NULL

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。