are MySQL functions possible?

[hiberphoptik]

is it possible to write a function that runs entirely in MySQL?

this is what I am trying to do:

|---------|

| num |

| 001 |

---

I want to create a MySQL statement/query that will take that number, increment it by 1 and when it gets to 999 start back over at 001

I know i can do this with PHP and many other languages, but I have to be able to do it with SQL for the project Im working on.

First:
is it possible to incrememnt that number and have it restart at 999 using nothing but a SQL query?

second:
if this is possible can it be written as a funciton that MySQL can call to??

TIA!!!

[weekender]

no, you can use variables within mysql, but the scope of the variable will only be that instance of the sql query on that row, i think; there are no mysql functions.

why does it have to be within mysql? let us know and maybe somebody will be able to suggest an alternative

ads

[dnszero]

Yes it is possible, but not pretty. You could do something like:

UPDATE tablename SET num = SUBSTRING(LPAD(num+1, 5, 0) FROM -3);

This will perform like you asked, ie, once it gets 999+1 it will return to 0, but, like I said, it's not pretty. Effective yes, pretty no. (shrug)

As far as making it a MySQL function, yes it is possible, but only with the MAX release of the newer versions... They are supposed to have support for User Defined Functions, but I've never used it before.

[superwormy]

Yes, its possible. Go see the MySQL manual section about Extending MySQL.