Find a column which subtracts another column

Question

In sql-server, There are three column in same table,i want to make Column_C from Column_A and Column_B i want to get in same table in sql

 Column_A  Column_B Column_C
    1,2,3,4      2,3
     1,2,3        1



    Column_A  Column_B Column_C
     1,2,3,4      2,3  1,4
     1,2,3        1    2,3

You should not store comma separated values in a single column. — a_horse_with_no_name, Mar 14 '18 at 18:34
I have more than one elements in a single cell with comma seperator — Biddut, Mar 14 '18 at 18:37
Having more than one element in a single cell with comma seperator breaks one of the cardinal rules of database design. — Honeyboy Wilson, Mar 14 '18 at 18:51
Learn database design. That will be a nightmare later on. Even now you can't even do stuffs. What's the point of storing data in a certain way if you can't do anything with it. — Eric, Mar 14 '18 at 22:56
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — a_horse_with_no_name, Mar 15 '18 at 09:36

score 1 · Accepted Answer · answered Mar 15 '18 at 09:55

1

If you use Postgres, you can install the intarray extension, then the solution is as simple as:

select column_a, 
       column_b, 
       array_to_string(
          string_to_array(column_a,',')::int[] - string_to_array(column_b,',')::int[]
       , ',') as column_c
from badly_designed_table

despite the horrible design.

string_to_array(column_a,',')::int[] converts the string to an array.

The intarray extensions provides the - operator that removes elements from the first array that are contained in the second.

array_to_string() then converts the array back to a string.

answered Mar 15 '18 at 09:55

a_horse_with_no_name

497,550
91
775
843

Error: For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name – Biddut Mar 15 '18 at 11:18
@Biddut: which database product are you using? Sounds as if you are not using Postgres – a_horse_with_no_name Mar 15 '18 at 11:23

score 0 · Answer 2 · answered Mar 14 '18 at 22:39

0

I'm very unsure what the actual table looks like or the columns for that matter... But I'll give you a general response for now and you can let me know if you need something else:

SELECT (ColumnA - ColumnB) AS Column C
FROM tablename

But since your table isn't normalized, this is very confusing. As well, you should never store multiple values separated by a comma in your table, because you can do it, but it'll make SQL development SOOOO much harder.

answered Mar 14 '18 at 22:39

Jacob T Cuthbertson

19
1

Also, not sure whether you are adding, subtracting, multiplying, dividing, or what is happening with ColumnA and ColumnB. – Jacob T Cuthbertson Mar 14 '18 at 22:40
Error: The data types varchar and varchar are incompatible in the subtract operator. – Biddut Mar 15 '18 at 11:12

Alex Zen · Answer 3 · 2018-03-15T09:44:51.130

0

This should do it. It replaces Column_B's string present in Column_A with an empty string. It also deals with potential double commas and leading/trailing commas

SELECT Column_A,
       Column_B,
       LTRIM(
         RTRIM(
           REPLACE(
             REPLACE(Column_A,
                     Column_B,
                     ''),
             ',,',
             ','),
           ','),
         ',') AS Column_C
  FROM Table1

This is correct for Oracle SQL. If you are using a different DBMS, I think you just need to use the corresponding TRIM syntax.

edited Mar 15 '18 at 09:44

answered Mar 14 '18 at 22:42

Alex Zen

886
7
9

Error: The rtrim function requires 1 argument(s). – Biddut Mar 15 '18 at 11:04
@Biddut which DBMS are you using? – Alex Zen Mar 15 '18 at 11:11
sql database @Alex Zen – Biddut Mar 15 '18 at 11:13
@Biddut "SQL" is a query language, not a database product. Every relational database is a "SQL database" – a_horse_with_no_name Mar 15 '18 at 11:24
@Alex Zen Thanks – Biddut Mar 15 '18 at 12:27

Find a column which subtracts another column

3 Answers3