0

I have a string column in SQL Server that contains data of payment. The column contains two data separated by comma, but may sometimes contain only one. I need to be able to split this single column into two new columns and convert this to money and perform a sum of the two.

Sample Payment Column

    +---------+
    | Payment |
    +---------+
    | 1000    |
    +---------+
    | 500, 100|
    +---------+
    | 10,20   |
    +---------+

desired output

    +---------+---------+--------+-------+
    | Payment | split1  | split2 | total |
    +---------+---------+--------+-------+
    | 1000    | 1000    | 0      | 1000  |
    +---------+---------+--------+-------+
    | 500, 100| 500     | 100    | 600   |
    +---------+---------+--------+-------+
    | 10,20   | 10      | 20     | 30    |
    +---------+---------+--------+-------+

I'd like to query this in a single select statement without using a function.

Solution

I have formulated the solution based on the links provided by the commentators. Since I wanted not to use cursors or functions, here is the complete select statement that answers my question:

    SELECT 
    cast(substring('1000,789',1,CHARINDEX(',', '1000,789')-1) as decimal(10,2)) AS Payment1,
    cast(substring('1000,789',CHARINDEX(',', '1000,789')+1,len('1000,789')) as decimal(10,2)) 
    as Payment2,
    cast(substring('1000,789',1,CHARINDEX(',', '1000,789')-1) as decimal(10,2)) + 
    cast(substring('1000,789',CHARINDEX(',', '1000,789')+1,len('1000,789')) as decimal(10,2))
     as TotalPayment from payment;
Joey
  • 391
  • 3
  • 9
  • 28
  • I'd like to query this in a single query statement without using functions – Joey Aug 31 '17 at 02:43
  • Use something like this: select regexp_substr(val, '[^,]+', 1, 1) as val1,regexp_substr(val, '[^,]+', 1, 1) as val2 – Chetan_Vasudevan Aug 31 '17 at 02:45
  • I'd like to see this question reopened. The marked answers for both of the referenced threads are using cursors to split the string... Not exactly optimal solutions... Perhaps this thread could yield some better answers. – Jason A. Long Aug 31 '17 at 03:11
  • @JasonA.Long, you are correct. I have edited this post and shared my solution. I'm not sure if it is the optimal solution, but works fine for me and hope it helps other programmers. – Joey Aug 31 '17 at 05:33
  • @Joe, The main question is, is there a maximum number payment values that you need to be able to deal with? Is two the max? or could there be dozens? – Jason A. Long Aug 31 '17 at 05:38
  • @JasonA.Long only two. The system that accepts payments records it on the same column. Basically, it is a breakdown of payment for two particular items purchased at the same time. Table structure for this application needs a serious rehab. – Joey Aug 31 '17 at 06:00
  • -- sorry about the formatting... SELECT p.Payment, sv.Split1, sv.Split2, Total = sv.Split1 + sv.Split2 FROM #Payment p CROSS APPLY ( VALUES (CHARINDEX(',', p.Payment, 1)) ) s (Split) CROSS APPLY ( VALUES ( CAST(LEFT(p.Payment, ISNULL(NULLIF(s.Split, 0) - 1, 8000)) AS DECIMAL(9,2)), CAST(SUBSTRING(p.Payment, s.Split + 1, 8000) AS DECIMAL(9,2)) ) ) sv (Split1, Split2); – Jason A. Long Aug 31 '17 at 06:16

0 Answers0