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 string

Function :

CREATE function [dbo].[Fun_ExtractAlphanumeric] (
@string varchar(8000)
) 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
RETURN @newstring

Explanation :

- 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.



No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: