SQLServer – T-SQL: Converting any string to its equivalent ASCII numeric value

Sometimes you need to convert an string to an unique number identifier, for instance converting an existing string PK to a numeric PK. Here we provide a sample function which converts any string value to an unique numeric value by converting each character to its ASCII value:

Function ascii_value:

For instance, if you execute:

You’ll get:

Which is the translation of each character to its ASCII value:

Note I added the prefix “1” in order to capture initial ASCII codes which its length is smaller than 3 characters. Therefore, you could implement the reverse funcion applying CHAR function for each 3-char chunks, previously converting prefix “1” to “0”.