Split SQL Server Column value

Question

I have a SQL Server table which name is AbundanceImpact.

Monetary~50
Monetary~120
Monetary~200
Monetary~269.90
Monetary~125
Magnanimous~50
Monetary~22.05
Unlimited~500
Monetary~150
Monetary~300
Monetary~21.89
Monetary~10.95

So I want the all integer values only with SUM.

Does this answer your question? [How to split a single column values to multiple column values?](https://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values) — oniramarf, Feb 14 '20 at 09:05

score 1 · Answer 1 · answered Feb 14 '20 at 09:02

1

You can use charindex() :

select t.col, substring(t.col, charindex('~', t.col) + 1, len(t.col)) as val
from table t;

answered Feb 14 '20 at 09:02

Yogesh Sharma

49,870
5
26
52

Thanks for your time, yeah it work but i want also sum values of all records. – Waqas Ali Feb 14 '20 at 09:16

score 0 · Accepted Answer · answered Feb 14 '20 at 09:15

0

Try this below logic-

DEMO HERE

SELECT 
SUM(
    CAST(
        RIGHT(
            Column_name,
            LEN(Column_name) - CHARINDEX('~',Column_name,0)
        ) 
        AS FLOAT
    )
)
FROM AbundanceImpact

answered Feb 14 '20 at 09:15

mkRabbani

16,295
2
15
24

Nguyễn Văn Phong · Answer 3 · 2020-02-14T09:27:48.857

0

Demo on db<>fiddle

SELECT SUM(
           CAST(
                RIGHT(Value, 
                            LEN(Value) - (CHARINDEX('~', Value))) 
                AS  Decimal(18, 2)
               )
      ) Total
FROM 
(
    VALUES
    ('Monetary~50'),
    ('Monetary~120'),
    ('Monetary~200'),
    ('Monetary~269.90'),
    ('Monetary~125'),
    ('Magnanimous~50'),
    ('Monetary~22.05'),
    ('Unlimited~500'),
    ('Monetary~150'),
    ('Monetary~300'),
    ('Monetary~21.89'),
    ('Monetary~10.95')
)v(Value)

Output

edited Feb 14 '20 at 09:27

answered Feb 14 '20 at 09:18

Nguyễn Văn Phong

13,506
17
39
56

@Waqas Ali Did you check my answer? – Nguyễn Văn Phong Feb 14 '20 at 10:43

score 0 · Answer 4 · edited Feb 14 '20 at 11:20

0

Use this logic

right('Monetary~120', len('Monetary~120')-CHARINDEX('~','Monetary~120'))

for your column with sum function.

edited Feb 14 '20 at 11:20

Suraj Kumar

5,547
8
20
42

answered Feb 14 '20 at 10:03

Bhupendra kumar

5
5

2

Request you to provide the complete solution with a brief description. Also how if is better than others already available answer. – Suraj Kumar Feb 14 '20 at 11:22

score 0 · Answer 5 · answered Feb 14 '20 at 12:48

I would use stuff() for this:

select sum(try_convert(numeric(20, 4),
                       stuff(col, 1, charindex('~', col), '')
                      )
          )

If you actually want to ignore the fractional components, then you would use numeric(20, 0) -- or something similar. The 20 seems big enough for your data.

Split SQL Server Column value

5 Answers5