Get only alphanumeric content
This function will explain how to extract only alphanumeric values from the inputted text. It will be very useful in the projects where someone needs to remove special characters from the text and only need the text with alphanumeric value. ASCII values are used to verify the character with while loop. Loop will extract inputted string character one by one and verify its ASCII value.
Hi All,Introduction :
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.Purpose :
- Extract only alphanumeric content from the provided input string
- Remove special characters from the provided input stringFunction :
CREATE function [dbo].[Fun_ExtractAlphanumeric] (
@string varchar(8000)
) returns varchar(8000)
AS
BEGIN
DECLARE @newstring varchar(8000)
DECLARE @num int
DECLARE @code int
select @newstring = ''
select @num = 1
while @num < len(@string)+1
begin
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)
begin
set @newstring = @newstring + Substring(@string, @num, 1)
end
set @num = @num + 1
end
RETURN @newstring
ENDExplanation :
- 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.
Thanks.