Strip characters out of query

Question

Say I have a table called Person and a field called name. Data quality in the name field poor. I want to strip out all the none alpha characters e.g.

select Replace(Replace(Replace(Name,',',''),'&',''),'@') from Person
 where Replace(Replace(Replace(Name,',',''),'&',''),'@') = @Name

@Name is the variable passed in.

Using this there will be lots of Replace statements wrapped around each other. I have two questions:

What characters would you strip out of a name search e.g. so if someone searches for Ian O' John, it will find Ian O John.
Would you use another technique to do this e.g. PadIndex or regular expressions

How about [full-text search](http://technet.microsoft.com/en-us/library/ms142571.aspx)? — Crozin, Mar 14 '14 at 14:27

score 0 · Answer 1 · answered Mar 14 '14 at 14:41

You can do this.

CREATE FUNCTION [db].[StringNonAlphaCharacters](@Temp VARCHAR(1000))
Returns VARCHAR(1000)
AS
Begin

    Declare @KeepValues as VARCHAR(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')    
    Return @Temp
End

Strip characters out of query

1 Answers1