Hope someone can help.
I need to generate (at runtime so no functions or stored procedures allowed) a number from a string.
Let me elaborate. I am having to use a table to get the names of suppliers. The person who designed the table, for reasons known only to him, thought it was best to allow the system to generate a primary key for each supplier.
Except this table gets dropped and recreated every night.........so, today Aardvark Pet Supplies might have ID=1 and Acturis Builders might have ID=2. But if a new supplier is created called Abacus Software, when the table is recreated then that would then have ID=2 and Acturis Builders would have ID=3.
So I'm having to use the supplier name for part of a composite key and I'd rather use a numeric identifier.
Is there a way to take Aardvark Pet Supplies and create a unique number from the letters?
I have been messing about with ASCII and SUBSTRING but seem to be stuck.
Any advice greatly appreciated.