How to extract alphanumeric characters from a string?

This is possible by using the combination of PATINDEX() and STUFF() functions.

Step 1: Declare a Parameter and Store the string in it.

Step 2: Search for Alphanumeric characters from the string by using PATINDEX. If exists, just loop the following statements.

Step 3: Replace the invalid characters with a blank by using STUFF function and update the same in the parameter.

Step 4: Display the modified string.

SET @Text = ‘I Love!@#$%%^&*() IN<>?:”{}|DIA.’
WHILE PATINDEX(‘%[^A-Za-z0-9 ]%’,@Text) <> 0
SET @Text = STUFF(@Text,PATINDEX(‘%[^A-Za-z0-9 ]%’,@Text),1,)
SELECT @Text AS [AlphaNumeric]


Extract Alphanumeric

Note: I am not excluding blank spaces from the above string. If you want to remove these too, just use %[^A-Za-z0-9]%  in PATINDEX. 

Latest Comments
  1. tai ionline mien phi

    Aw, it was quite a good post. In notion I have to place in writing similar to this additionally – taking time and actual effort to generate a really good article… but what can I say… I procrastinate alot and also by no indicates appear to go completed.

  2. Venkat

    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.