时间戳

首页 > 解决方案 > 如何在 SQL Server 中转换一些记录

sql - 如何在 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 的代码是：

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

标签： sqlsql-servertsql

解决方案

只需将 替换为-，.因为PARSENAME()仅适用于'.'，而不适用'-'

WITH TBL AS
(
  SELECT       '1-829549-305-117' Str
  UNION SELECT '1-1196585-305-119'
  UNION SELECT '119.305.1983984.1' 
  UNION SELECT '1-224594-305-121'
  UNION SELECT '1-1999987-305-121'
  UNION SELECT '122.306.113416.1' 
  UNION SELECT '1-158059-305-122'
  UNION SELECT '1-1083888-305-126'
),
CTE AS
(
  SELECT Str,
         CASE WHEN CHARINDEX('.', Str) > 0 THEN
                   Str
              ELSE REPLACE(Str, '-', '.')
         END Str1
         ,
         CASE WHEN CHARINDEX('.', Str) > 0 THEN '.' ELSE '-' END Sep
  FROM TBL
)
SELECT Str,
       CONCAT(PARSENAME(Str1,1), Sep,
              PARSENAME(Str1,2), Sep,
              PARSENAME(Str1,3), Sep,
              PARSENAME(Str1,4)
              ) Result

FROM CTE;

回报：

+-------------------+-------------------+
|        Str        |      Result       |
+-------------------+-------------------+
| 1-1083888-305-126 | 126-305-1083888-1 |
| 1-1196585-305-119 | 119-305-1196585-1 |
| 1-158059-305-122  | 122-305-158059-1  |
| 119.305.1983984.1 | 1.1983984.305.119 |
| 1-1999987-305-121 | 121-305-1999987-1 |
| 122.306.113416.1  | 1.113416.306.122  |
| 1-224594-305-121  | 121-305-224594-1  |
| 1-829549-305-117  | 117-305-829549-1  |
+-------------------+-------------------+

现场演示

如果你有 SQL Server 2017，那么你可以使用CONCAT_WS()as

SELECT Str,
       CONCAT_WS(Sep, 
              PARSENAME(Str1,1), 
              PARSENAME(Str1,2), 
              PARSENAME(Str1,3), 
              PARSENAME(Str1,4)
              ) Result

FROM CTE;

如果您希望分隔符始终存在，'-'则无需Sep，直接'-'

推荐阅读