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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
ALTER FUNCTION [dbo].[ascii_value] ( @string varchar(2000) ) RETURNS varchar(2000) AS BEGIN DECLARE @position int, @aux char(3), @myval varchar(2000)='1' SET @position = 1 WHILE @position <= DATALENGTH(@string) BEGIN SET @aux = ASCII(SUBSTRING(@string, @position, 1)) SET @myval = @myval+ replace(@aux,' ','0') SET @position = @position + 1 END RETURN @myval END |
For instance, if you execute: […]
