CODEWITHSUNDEEP
sqloracle-databaseoracle10g
VextoR
VextoR

Reputation: 5165

how to insert multiple values in one field?

I have a query:

    INSERT
INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222',
    'NEW',
    '1'
  )

this one works OK. But instead of '1' I want to insert multiple values in one field, like '1','2','3'

And usually you do it like this:

INSERT
INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222e',
    'NEW',
    '1,2,3'
  )

But how to do it if values will put there as '1','2','3'?

INSERT
INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222e',
    'NEW',
    '1','2','3'
  )

So, we can't change '1','2','3' (due of some automation) but we can add anything before and past this string. In result information in Reason field should be something like 1,2,3

How to do that?

Upvotes: 0

Views: 12059

Answers (4)

xHerickx
xHerickx

Reputation: 1

Try this

INSERT INTO I#journal
 (
   Type_,
   Mndnr,
   Obj,
   Status,
   Reason
 )
 VALUES
 (
   'PO',
   '0177',
   '000222',
   'NEW',
   '''1'',''2'',''3'''
 )

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Insert value replace(q'$'1','2','3'$', '''', '') Single quotes(') is the escape character. i.e.

INSERT
INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222',
    'NEW',
    replace(q'$'1','2','3'$', '''', '')
  );

Upvotes: 2

A.B.Cade
A.B.Cade

Reputation: 16905

Try:

INSERT
INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222e',
    'NEW',
    replace(q'['1','2','3']', q'[',']', '')  )

Upvotes: 1

swetha
swetha

Reputation: 130

try this INSERT.

INTO I#journal
  (
    Type_,
    Mndnr,
    Obj,
    Status,
    Reason
  )
  VALUES
  (
    'PO',
    '0177',
    '000222',
    'NEW',
    '1'
  ),(
    'PO',
    '0177',
    '000222e',
    'NEW',
    '1,2,3'
  )

Upvotes: 0

Related Questions