Here I am going to share a small SQL function. In many projects there is necessity of extracting alphanumeric content from the provided input. In that case we can use this function.
- Extract only alphanumeric content from the provided input string
- Remove special characters from the provided input string
CREATE function [dbo].[Fun_ExtractAlphanumeric] (
) returns varchar(8000)
DECLARE @newstring varchar(8000)
DECLARE @num int
DECLARE @code int
select @newstring = ''
select @num = 1
while @num < len(@string)+1
SET @code = Ascii(SubString(@String, @Num, 1))
--- ASCII: 48-57 (numbers 0-9)
--- ASCII: 65-90 (uppercase letters A-Z)
--- ASCII: 97-122 (lowercase letters a-z)
If (@code between 48 and 57) or (@code between 65 and 90) or (@code between 97 and 122)
set @newstring = @newstring + Substring(@string, @num, 1)
set @num = @num + 1
- This function is based on the ASCII value of the string
- It loops through each string character and verify its ASCII value. If it is matching with any of following criteria then it append that character in a new string other wise it just skip that character.
Numbers i.e. 0 to 9
Upper case alphabets i.e. A to Z
Lower case alphabets i.e. a to z
Hope you will find it helpful. Your comments are welcome and greatly appreciated.