SQL Server 2008 select statement that ignores non alphanumeric characters

I have an interesting SQL Server search requirement.

Say I have a table with Part Numbers as follows:

PARTNO    DESCRIPTION
------    -----------
ABC-123   First part
D/12a92   Second Part

How can I create a search that will return results if I search, say, for 'D12A'?

I currently have a full text search set up for the description column, but I am looking to find parts that match the part no even when users don't include the / or - etc.

I'd rather do this in a single SQL statement rather than creating functions if possible as we only have read access to the DB.

Solution

You could do something like:

SELECT * FROM PART_TABLE
WHERE REPLACE(REPLACE(PARTNO,'/', ''),'-','') LIKE '%D12A%'

This would work for the 2 characters you specified and could be extended for more character like so:

SELECT * FROM PART_TABLE
WHERE REPLACE(REPLACE(REPLACE(PARTNO,'/', ''),'-',''),*,'') LIKE '%D12A%'

Probably not the most elegant of solutions unless your special characters are limited. Otherwise I'd suggest writing a Function to strip out non-alphanumeric characters.

Here is an example of such a function:

CREATE FUNCTION dbo.udf_AlphaNumericChars
(
@String     VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

  DECLARE @RemovingCharIndex INT
  SET @RemovingCharIndex = PATINDEX('%[^0-9A-Za-z]%',@String)

  WHILE @RemovingCharIndex > 0
  BEGIN
    SET @String = STUFF(@String,@RemovingCharIndex,1,'')
    @RemovingCharIndex = PATINDEX('%[^0-9A-Za-z]%',@String)
  END

  RETURN @String

END


------- Query Sample (untested)---------
SELECT *
FROM PART_TABLE
WHERE DBO.udf_AlphaNumericChars(PARTNO) LIKE '%D12A%'

Taken From: http://sqlserver20.blogspot.co.uk/2012/06/find-alphanumeric-characters-only-from.html