如何从逗号分隔的字符串中减去每个数字？

I have a table with a string field where than can be one number or mulitple numbers (delimited by a comma). I need to find the difference between the values (when converted to an integer) and an unspecified value. For simplicity sake for this question, I'll just say the value to be compared is a static value of 10.

我有一个带字符串字段的表,其中可以是一个数字或多个数字(用逗号分隔)。我需要找到值(转换为整数时)和未指定值之间的差异。为了简单起见这个问题,我只想说要比较的值是静态值10。

Example Table:

iId vchStringNumbers vchSubtractedStringNumbers 1 20, 30, 40 2 50 3 20

i vdStringNumbers vchSubtractedStringNumbers 1 20,30,40 2 50 3 20

Desired Results:

iId vchStringNumbers vchSubtractedStringNumbers 1 20, 30, 40 10, 20, 30 2 50 40 3 20 10

i vdStringNumbers vchSubtractedStringNumbers 1 20,30,40 10,20,30 2 50 40 3 20 10

Is there a way to accomplish this in SQL? If it would be eaiser in excel or something like that, feel free to answer as well.

有没有办法在SQL中实现这一点?如果它是excel中的eaiser或类似的东西,也可以随意回答。

1 个解决方案

#1

Place you CS data in an Excel column, Select the cells and run this tiny VBA macro:

将CS数据放在Excel列中,选择单元格并运行这个微小的VBA宏:

Sub SubtractCSV()
    Dim r As Range
    For Each r In Selection
        ary = Split(r.Value, ",")
        For i = LBound(ary) To UBound(ary)
            ary(i) = CLng(Trim(ary(i))) - 10
        Next i
        r.Offset(0, 1).Value = Join(ary, ",")
    Next r
End Sub