SQL Server 2008 select statement that ignores non alphanumeric characters -

i have interesting sql server search requirement.

say have table part numbers follows:

partno    description ------    ----------- abc-123   first part d/12a92   second part 

how can create search return results if search, say, 'd12a'?

i have full text search set description column, looking find parts match part no when users don't include / or - etc.

i'd rather in single sql statement rather creating functions if possible have read access db.

you like:

select * part_table replace(replace(partno,'/', ''),'-','') '%d12a%' 

this work 2 characters specified , extended more character so:

select * part_table replace(replace(replace(partno,'/', ''),'-',''),*,'') '%d12a%' 

probably not elegant of solutions unless special characters limited. otherwise i'd suggest writing function strip out non-alphanumeric characters.

here example of such function:

create function dbo.udf_alphanumericchars ( @string     varchar(max) ) returns varchar(max) 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 * part_table dbo.udf_alphanumericchars(partno) '%d12a%' 

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