Generate number from word?

Transact-SQL
1

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.

2

One way is to use BINARY_CHECKSUM.
Also exists CHECKSUM

declare @vcName as VARCHAR(50) = 'Abacus Software ' 
SELECT BINARY_CHECKSUM(@vcName)
1 Like
3

The best is to stop the job that is droping/recreating the table.

One way to solve this is by deny /revoke the permission to that .
Reading from BOL

Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

Please, keep in mind that by revoking a permission, that process will fail and if it had more stmts, than all will fail.

1 Like
4

Yeah I don't have any influence over whether the table is dropped or not; I have to work with it as it is :frowning:

However, your earlier answer works perfectly, many thanks.

5

Just be very aware that checksums can and do have overlaps. For example, run the following as a simple example.

 SELECT  CS1 = CHECKSUM('A352KD')
        ,CS2 = CHECKSUM('A352NT')
;
1 Like