How to fetch only characters in a string for comparison?

chokka
chokka used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer
Commented:
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) )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Good AS VARCHAR(50) = '%[^a-zA-Z ]%';
    WHILE PATINDEX(@Good, @Temp) > 0
    SET @Temp = STUFF(@Temp, PATINDEX(@Good, @Temp), 1, '');
    RETURN @Temp;
END;

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)
chokkaStudent

Author

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 Developer
Commented:
Remove the space after the 'Z' from '%[^a-zA-Z ]%' (i.e. '%[^a-zA-Z]%')
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

chokkaStudent

Author

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 Developer

Commented:
With the space removed from the pattern, you'd be comparing TomSmith with TomSmith
chokkaStudent

Author

Commented:
Yes it worked, Thank you so much for that
chokkaStudent

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial