如何转换SQL Server中的一些记录

2021-09-10 00:00:00 sql tsql sql-server

我有一列包含如下值:

## codel ##
1-829549-305-117
1-1196585-305-119
119.305.1983984.1 // this record
1-224594-305-121
1-1999987-305-121
122.306.113416.1 // this record
1-158059-305-122
1-1083888-305-126

将 119.305.1983984.1 转换为 1.1983984.305.119 的代码是:

Code for convert 119.305.1983984.1 to 1.1983984.305.119 is :

DECLARE @myvar varchar(20);  
SET @myvar = '119.305.1983984.1';  

SELECT 
    CONCAT(PARSENAME(@myvar, 1), '-',
           PARSENAME(@myvar, 2), '-',
           PARSENAME(@myvar, 3), '-',
           PARSENAME(@myvar, 4))

输出应该是:

## codel ##
1-829549-305-117
1-1196585-305-119
1-1983984-305-119 // this record has changed
1-224594-305-121
1-1999987-305-121
1-113416-306-122 // this record has changed
1-158059-305-122

推荐答案

你只需要添加这段代码

 SELECT [Codel],
case WHEN  (CHARINDEX('.',[Codel]) > 0) then 

    CONCAT(PARSENAME([Codel], 1), '-',
           PARSENAME([Codel], 2), '-',
           PARSENAME([Codel], 3), '-',
           PARSENAME([Codel], 4))  
           else [Codel] end
            true_template

  FROM [TestDB].[dbo].[CodelTbl]

相关文章