sql server中將一個欄位根據某個字元拆分成多個欄位顯示

標籤：when   字串   int   統一   span   art   string   sel   update   

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 那麼此時小區名欄位的資訊就是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 [email protected]分隔字元所在的位置的下標 @startsplit 每次分割開始的位置    SELECT @index=CHARINDEX(@fengefu,@str),@startsplit=1,@id=1    WHILE @index>0    BEGIN        IF @id>1  --第一次迴圈之後 都執行這個方法體中的代碼        BEGIN             SELECT @[email protected]+LEN(@fengefu) --分割開始位置等於 之前的字元下標位置+字元本身的長度            SELECT @index=CHARINDEX(@fengefu,@str,@startsplit)                    END         IF @index>0   --第一次迴圈 截取位置從@startsplit=1開始              BEGIN                 INSERT INTO @table VALUES (@id,SUBSTRING(@str,@startsplit,@[email protected])) --要截取的字串   開始位置  要截取的長度            END         ELSE         BEGIN   --最後一次迴圈  此時由於匹配不到分割字元 @index=0 截取剩餘的字串            INSERT INTO @table VALUES (@id,SUBSTRING(@str,@startsplit,LEN(@str)[email protected]+1))        END         SELECT @[email protected]+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中將一個欄位根據某個字元拆分成多個欄位顯示