By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

problem with string concatenation in auto-incremented field

P: 45
hi all
i m using oracle9i and php4 to developa webbased application.
i wrote the code for auto increment in primary key field.but now i want to add a string value with that increment field when a new insert is occured.

so is there anybody to solve it out by providing the sample source code.

bye-
Mar 3 '08 #1
Share this Question
Share on Google+
8 Replies


ronverdonk
Expert 2.5K+
P: 4,258
This is an Oracle SQL question. I will move it to that forum.

moderator
Mar 3 '08 #2

amitpatel66
Expert 100+
P: 2,367
hi all
i m using oracle9i and php4 to developa webbased application.
i wrote the code for auto increment in primary key field.but now i want to add a string value with that increment field when a new insert is occured.

so is there anybody to solve it out by providing the sample source code.

bye-
What is the datatype of your primary key field? If it is number then you will not be able to concatenate a string value to an inceremented value and insert it. If the datatype is varchar then it is possible to insert a concatenated string value into a primary key field

Pelase provide your table structure for reference of our experts
Mar 3 '08 #3

P: 45
hello,
here is my table :
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE MRR_ENTRY2
  2. (
  3.   MRR_NO            VARCHAR2(17)primary key,
  4.   MRR_DATE          DATE,
  5.   ACCOUNT_CODE      VARCHAR2(17),
  6.   AMOUNT            NUMBER(10,2),
  7.   CHECK_NO          VARCHAR2(12),
  8.   CHECK_ISSUE_DATE  DATE,
  9.   DRAWN_ON_BANK_ID  VARCHAR2(10),
  10.   BANK_BRANCH_ID    VARCHAR2(10),
  11.   AGAINST           VARCHAR2(50),
  12.   PMT_MODE_ID       VARCHAR2(10)                NOT NULL,
  13.   PARTY_CODE        VARCHAR2(20)                NOT NULL,
  14.   COMMISSION        NUMBER(10,2),
  15.   USERNAME          VARCHAR2(20),
  16.   LOCATION_ID       VARCHAR2(10),
  17.   SL_NO             VARCHAR2(10),
  18.   REMARKS           VARCHAR2(500),
  19.   PARTY_GL_CODE     VARCHAR2(20)                NOT NULL,
  20.   DO_NO             VARCHAR2(17),
  21.   DO_DATE           DATE
  22. )

the primary key field is incrementing automatically but i also want to add a sting with incremented value in that field when inserting from html page depending on which string is selected by user.

so can u now help me? plz..it's very very important .

thanks.
Mar 9 '08 #4

debasisdas
Expert 5K+
P: 8,127
What is the problem you are facing ?

You should be able to concatenate the string with the number and store to the database.
Mar 10 '08 #5

amitpatel66
Expert 100+
P: 2,367
hello,
here is my table :
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE MRR_ENTRY2
  2. (
  3.   MRR_NO            VARCHAR2(17)primary key,
  4.   MRR_DATE          DATE,
  5.   ACCOUNT_CODE      VARCHAR2(17),
  6.   AMOUNT            NUMBER(10,2),
  7.   CHECK_NO          VARCHAR2(12),
  8.   CHECK_ISSUE_DATE  DATE,
  9.   DRAWN_ON_BANK_ID  VARCHAR2(10),
  10.   BANK_BRANCH_ID    VARCHAR2(10),
  11.   AGAINST           VARCHAR2(50),
  12.   PMT_MODE_ID       VARCHAR2(10)                NOT NULL,
  13.   PARTY_CODE        VARCHAR2(20)                NOT NULL,
  14.   COMMISSION        NUMBER(10,2),
  15.   USERNAME          VARCHAR2(20),
  16.   LOCATION_ID       VARCHAR2(10),
  17.   SL_NO             VARCHAR2(10),
  18.   REMARKS           VARCHAR2(500),
  19.   PARTY_GL_CODE     VARCHAR2(20)                NOT NULL,
  20.   DO_NO             VARCHAR2(17),
  21.   DO_DATE           DATE
  22. )

the primary key field is incrementing automatically but i also want to add a sting with incremented value in that field when inserting from html page depending on which string is selected by user.

so can u now help me? plz..it's very very important .

thanks.
Are you looking at updating existing values in the table with concatenating them with the string?

or

hence forth concatenate the string and then insert?
Mar 10 '08 #6

P: 45
Thanks for ur reply

i want to concate string with primary key and then insert in db .

u can also show me the code for updating existing values with concatenation

bye-
Mar 17 '08 #7

amitpatel66
Expert 100+
P: 2,367
Thanks for ur reply

i want to concate string with primary key and then insert in db .

u can also show me the code for updating existing values with concatenation

bye-
Expand|Select|Wrap|Line Numbers
  1.  
  2. update table1 SET col1 = COL1||'Welcome'
  3.  
  4.  
This will update all the values of col1 with col1 concatenated with the string 'Welcome'.
Mar 17 '08 #8

QVeen72
Expert 100+
P: 1,445
Hi,

SAY YOU HAVE ALREADY STORED NUMERIC FROM 1, 2, 3
and Now you want to change them to
A00001
A00002
A00003

Run this query:

Expand|Select|Wrap|Line Numbers
  1. UPDATE MRR_ENTRY
  2. SET MRR_NO = 'A' || REPLACE (LPAD(MRR_NO, 5), ' ', '0')
  3.  
To get Next highest value:
Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(MRR_NO)  FROM MRR_ENTRY
  2. WHERE MRR_NO LIKE 'A%'
  3.  
Regards
Veena
Mar 17 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.