How to fetch only characters in a string for comparison?

How to fetch only characters in a string for comparison?

I have a Table Name : Person with a column as Name.

Name in the Table  = John (Smith)

Name field from the Inbound file has the value = John Smith
or )
Name field from the Inbound file has the value = John "Smith"
or )
Name field from the Inbound file has the value = John 'Smith'
I will have to compare both names by removing the special characters and space.

select name from person where name = @NamefromInboundfile
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
As I understand, you can have special characters in either the name in the table or in the parameter values you are trying to match. This can be quite a challenge, not because it can't be done but because you will be eliminating the possibility of using any indexes in your name match. I would look at the possibility of cleansing the data you are putting into your table, so that it contains no special characters. I would consider creating a function such as this:
ALTER FUNCTION dbo.CleanseString( @Temp VARCHAR(MAX) )
    DECLARE @Good AS VARCHAR(50) = '%[^a-zA-Z ]%';
    WHILE PATINDEX(@Good, @Temp) > 0
    SET @Temp = STUFF(@Temp, PATINDEX(@Good, @Temp), 1, '');
    RETURN @Temp;

Open in new window

If there are special characters on both sides (table and variable, use:
select name from person where dbo.CleanseString(name) = dbo.CleanseString(@NamefromInboundfile)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chokkaStudentAuthor Commented:
@Doug, Thank you. But i have a problem ...

select 'Tom    Smith',dbo.Cleansestring('Tom    Smith')

Open in new window

When we have space in the middle of the name , we need to filter the space / special characters and have only the actual characters for comparison.

Example :-

Existing data might be having two spaces in the middle of the name. However, Inbound data has only one space.
If the Alphabets are equal, then we will have consider that record is matching.
Doug BishopDatabase DeveloperCommented:
Remove the space after the 'Z' from '%[^a-zA-Z ]%' (i.e. '%[^a-zA-Z]%')
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

chokkaStudentAuthor Commented:
Challenge is , we are comparing the existing record to the Incoming record.

In the existing record we might have special characters or empty space ..

Tom 'Smith'
Tom "Smith"
Tom     Smith

However in the Incoming record, we might be having as Tom Smith

When we compare based on Alphabets, they are same. Special Characters or Empty space makes the comparison difference.
Doug BishopDatabase DeveloperCommented:
With the space removed from the pattern, you'd be comparing TomSmith with TomSmith
chokkaStudentAuthor Commented:
Yes it worked, Thank you so much for that
chokkaStudentAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.