主页 / dba / 问题 / 90565
Accepted
jhowe
Asked: 2015-01-30 08:59:49 +0800 CST

SQL权重乘法

  • 772

大家好，我在表格中有两列：

+--------------------+----------------+
| Units/Pk           | PackWeight/Vol |
+--------------------+----------------+
| 1.0000000000000000 | 150g           |
+--------------------+----------------+
| 1.0000000000000000 | 150g           |
+--------------------+----------------+
| 1.0000000000000000 | 114g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 1.0000000000000000 | 200g           |
+--------------------+----------------+
| 4.0000000000000000 | 100g           |
+--------------------+----------------+
| 4.0000000000000000 | 125g           |
+--------------------+----------------+
| 4.0000000000000000 | 125g           |
+--------------------+----------------+
| 1.0000000000000000 | 500g           |
+--------------------+----------------+

我需要将它们相乘以获得结果以替换 PackWeight/Vol ie Units/Pk * PackWeight/Vol AS PackWeight/Vol 中的值。我该怎么做呢？即单位为4的结果分别为400g、500g、500g。其他结果将保持不变，因为单位为 1。单位/Pk 是数字，包装重量是 nvarchar。谢谢！

sql-server t-sql

1 个回答

  1. Best Answer

    这是实现此目的的一种方法，但我同意 JNK 在评论中的观点，即它的扩展性非常差并且性能相同：

        SELECT CASE WHEN ISNULL([PackWeight/Vol], '') = '' OR PATINDEX('%[a-Z]%', [PackWeight/Vol]) = 0 
THEN '' 
ELSE CAST(SUBSTRING([PackWeight/Vol], 1, PATINDEX('%[a-Z]%', [PackWeight/Vol]) - 1)  * [Units/Pk] AS VARCHAR(256)) + 
               SUBSTRING([PackWeight/Vol], PATINDEX('%[a-Z]%', [PackWeight/Vol]),  LEN([PackWeight/Vol]) + 1 - PATINDEX('%[a-Z]%', [PackWeight/Vol]))
    END
        FROM table
    • 3

相关问题