Insert with prefix

2005-07-27 Thread Scott Purcell 
Hello,

I have the following dilemma, that I do not know how to handle.

I have the need for a table where I create a identifier. The identifier 
consists of a two character string, eg: UP + the next AUTO_INCREMENT 
Here is what I have.

   category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Table should look like this:
UP01
UP02
UP03

So the problem is, how can I grab the auto_increment number, then append the 
prefix while doing an insert?

Can anyone give me some advice on this problem?

Thanks,
Scott

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Insert with prefix

2005-07-27 Thread Eric Bergen 

Try this:
concat('UP', lpad(category_id, 6, '0'));

Scott Purcell wrote:


Hello,

I have the following dilemma, that I do not know how to handle.

I have the need for a table where I create a identifier. The identifier consists of a two character string, eg: UP + the next AUTO_INCREMENT 
Here is what I have.


  category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Table should look like this:
UP01
UP02
UP03

So the problem is, how can I grab the auto_increment number, then append the 
prefix while doing an insert?

Can anyone give me some advice on this problem?

Thanks,
Scott

 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Insert with prefix

2005-07-27 Thread SGreen 
Eric Bergen [EMAIL PROTECTED] wrote on 07/27/2005 05:03:40 PM:

 Try this:
 concat('UP', lpad(category_id, 6, '0'));
 
 Scott Purcell wrote:
 
 Hello,
 
 I have the following dilemma, that I do not know how to handle.
 
 I have the need for a table where I create a identifier. The 
 identifier consists of a two character string, eg: UP + the next 
 AUTO_INCREMENT 
 Here is what I have.
 
category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
 Table should look like this:
 UP01
 UP02
 UP03
 
 So the problem is, how can I grab the auto_increment number, then 
 append the prefix while doing an insert?
 
 Can anyone give me some advice on this problem?
 
 Thanks,
 Scott
 
  
 

Create two fields, one to hold the UP characters, the other is a regular 
auto_increment field. Then combine the two in your SELECT fields. Use the 
formula that Eric provided and you will get the OUTPUT you want. Otherwise 
you will not be able to use an auto_increment value. Rather, you will need 
to create your own values as you generate your records (much slower)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine