String Comparision in SQL Server 2000? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String Comparision in SQL Server 2000?

Author

Topic

nrusa
Starting Member

4 Posts

Posted - 2008-02-15 : 20:48:56

HI,
I have "Customer" table in that table couple of customers are in same Address. I need to find out them. But, while seeing the data, I understand that user entered data in different format, But the address is same.
Ex: 1) 2632 LUCY GARRETT RD
2) 2632, LUCY GARRETT RD
Above 2 street names are same, But my SQL written 2 rows. Is there any why to skip "," and " " ( Space ) .
1) There are 2 spaces between "LUCY" and "GARRETT" but in 2) One space in between "LUCY" and "GARRETT". I am looking for the comparison like if characters and numbers are same then the street name is same.
Pls help me to fix the issue or Pls guide me how to handle this.

Thanks in advance
NR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 01:37:08

use like this for comparison
LTRIM(RTRIM(REPLACE(REPLACE(Address1,',',''),' ','')))=LTRIM(RTRIM(REPLACE(REPLACE(Address2,',',''),' ','')))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 03:33:11

quote:
Originally posted by visakh16

use like this for comparison
LTRIM(RTRIM(REPLACE(REPLACE(Address1,',',''),' ','')))=LTRIM(RTRIM(REPLACE(REPLACE(Address2,',',''),' ','')))

You dont need TRIMs as you replace space with empty string

Madhivanan

Failing to plan is Planning to fail

nrusa
Starting Member

4 Posts

Posted - 2008-02-19 : 21:59:23

I appreciate your help Visakh. It works fine..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 00:25:43

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

use like this for comparison
LTRIM(RTRIM(REPLACE(REPLACE(Address1,',',''),' ','')))=LTRIM(RTRIM(REPLACE(REPLACE(Address2,',',''),' ','')))

You dont need TRIMs as you replace space with empty string

Madhivanan

Failing to plan is Planning to fail

yup. thats true madhi

. Nice spot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 01:05:12

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

use like this for comparison
LTRIM(RTRIM(REPLACE(REPLACE(Address1,',',''),' ','')))=LTRIM(RTRIM(REPLACE(REPLACE(Address2,',',''),' ','')))

You dont need TRIMs as you replace space with empty string

Madhivanan

Failing to plan is Planning to fail

yup. thats true madhi . Nice spot

No Problem. This happens to all

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources