A Query to return Alphanumeric

Answer 1

use Replace function of sql server

Sql Server Tips – Removing or Replacing non-alphanumeric characters in strings

Answer 2

I don't think this is possible with T-SQL only in a neat way. But you could easily expose such a functionality through a .NET Assembly to the SQL-Server. There several examples on this topic over the net.

Answer 3

You could try the patindex function. For example with just selecting the FirstName this will remove the first occurrence of non alphanumeric:

SELECT replace(FirstName, substring(FirstName, patindex('%[^a-zA-Z0-9]%', FirstName), 1), '') FROM CONTACTS

To expand this to removing all occurrences, move the patindex call into a function as mentioned here:

CREATE FUNCTION CleanVarchar(@Temp VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN

    WHILE PATINDEX('%[^a-zA-Z0-9]%', @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z^0-9]%', @Temp), 1, '')

    RETURN @TEmp
END

Finally call the function

Select CleanVarchar(FirstName),CleanVarchar(Surname),CleanVarchar(NationalID) From Contacts