0

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
Biddut
  • 418
  • 1
  • 6
  • 17
  • 4
    Please normalize your schema before it is not too late :) – Lukasz Szozda Mar 14 '18 at 18:30
  • 3
    You should not store comma separated values in a single column. –  Mar 14 '18 at 18:34
  • Wait, I have so many questions. – stackFan Mar 14 '18 at 18:36
  • I have more than one elements in a single cell with comma seperator – Biddut Mar 14 '18 at 18:37
  • 1
    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
  • these are varchar – Biddut Mar 14 '18 at 18:52
  • 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
  • 2
    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`, ... –  Mar 15 '18 at 09:36

3 Answers3

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.

  • 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 –  Mar 15 '18 at 11:23
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.

Jacob T Cuthbertson
  • 19
  • 1
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.

Alex Zen
  • 906
  • 7
  • 9