469,950 Members | 2,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Help of auto_increment

Hi, how can I add char to auto_increment? For example, the
auto_increment is for integer, I want to add a character at the front
of that number. Ex:
create table abs (UserID INT(3) NOT NULL AUTO_INCREMENT, name varchar(15),
primary key(UserID));insert into abs(name) VALUES ('Elton'), ('Andy'), ('Peter');
select * from abs;


The output is:
+ -------+--------+
| UserID | name |
+--------+--------+
| 1 | Elton |
| 2 | Andy |
| 3 | Peter |
+ -------+--------+

The output I want is:
+ -------+--------+
| UserID | name |
+--------+--------+
| U1 | Elton |
| U2 | Andy |
| U3 | Peter |
+ -------+--------+
Notice that I added 'U' at the front of the UserID, is it possible to
do that?
What is the command for that? Thanks
Best Regards,
Hau Jyn
Jul 20 '05 #1
2 1759
hjyn wrote:
Hi, how can I add char to auto_increment?


As far as I can tell from the documentation, auto_increment can be
applied only to integer type columns.

If you want a 'U' at the start of the identifier, you can do it as an
expression in the select:

SELECT CONCAT('U', abs) AS abs, ...
FROM abs;

Other options might be to avoid the use of auto_increment, and provide a
unique primary key value manually. Or else define an integer field as
the auto_increment field, and then another field as the identifier with
the character prefix. Then, every time you insert a value to your
table, follow that operation with an update forcing the second
identifier column to have the value you describe:
UPDATE abs SET abs2 = CONCAT('U', abs);

Regards,
Bill K.
Jul 20 '05 #2
Thanks Bill, your solution help me a lot.

Regards,
Hau Jyn
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Frank | last post: by
9 posts views Thread by Bart Van der Donck | last post: by
reply views Thread by Mike Chirico | last post: by
reply views Thread by Shailesh | last post: by
2 posts views Thread by hjyn | last post: by
7 posts views Thread by Jim | last post: by
3 posts views Thread by Pir | last post: by
3 posts views Thread by Marty | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.