Formatting VARCHAR 1

[Nematoth]

I have a VARCHAR value in a table that I want to retrieve and increment.

I have tried this:

Step 1 - Retrieve the value (fine, no problem)

mysql> select MAX(sub_no)AS sub_no from proj_sub;
+---------+
| sub_no |
+---------+
| 1002-02 |
+---------+
1 row in set (0.00 sec)

Step 2. (this is where I'm stuck)

I want to format this using mySQL to read 1002-03. Is this possible or will I have to use PHP to do it?

Thomas Shearer
Multimedia Developer

http://www.pixelimage.co.uk

 

[sleipnir214]

It can be on in a SQL query, but the query isn't very pretty:

update foo set bar = concat(substring(bar, 1, locate('-', bar) - 1), '-', substring(bar, locate('-', bar) + 1) + 1)


Why not use two columns, one to store each part? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[Nematoth]

Thanks for the suggestion Sleipnir! I seem to be saying that quite a lot recently. You're always helping me out!

I think what I'll do is retrieve the first part of the string, '1002', from the project table and have just 02 in the sub_proj table like you suggest and then it's easy enough to increment that on its own.

Cheers! Thomas Shearer
Multimedia Developer

http://www.pixelimage.co.uk

 

[sleipnir214]

No prob. It's why I hang out here.

Remember, too, that a MySQL index can span multiple columns. So you can index the table on both those columns at once -- which may be useful for speeding up project information retrievals. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!