Select distinct replace where troubles

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 2005 Forums

Transact-SQL (2005)

Select distinct replace where troubles

Author

Topic

supernoob
Starting Member

6 Posts

Posted - 2010-03-04 : 15:16:21

I'm new to SQL, so sorry if this sounds really dumb.

I'm trying to get a count of distinct field matches between two tables where both fields have certain characters stripped out of them. I've managed to strip out all the bad characters with nested replace statements, but my main problem is trying to get a count of distinct matches using where.

This is all that I've managed to get working:

SELECT DISTINCT * FROM table1, table2
  where
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(
             table1.Name, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     '') =
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(
             table2.Name, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     '')

This mess doesn't seem to even actually remove the characters I want (I know the removes work...).

Thanks for the help.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-04 : 15:29:20

Seems to work ok...

Are you trimming and then removing any other unwanted spaces?

Declare @t1 Table (name1 VarChar(20), name2 VarChar(20))

Insert @t1
Select 'ads''''&.23', '1/1/2--010'

Select *, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(
             name1, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     ''), Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(
             name2, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     '') from @t1

Results -

name1	name2	(No column name)	(No column name)
ads''&.23	1/1/2--010	ads23	112010

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-04 : 15:37:59

Also be careful with those 'and' and 'plus' replaces...

Name 1 = Andy
Name 2 = Plusy

After replace...

Name 1 = y
Name 2 = y

You may not want those being the same...

Same with

Name 1 = 'Andover+you'
Name 2 = 'overyou'

becomes...

Name 1 = 'overyou'
Name 2 = 'overyou'

supernoob
Starting Member

6 Posts

Posted - 2010-03-04 : 15:40:31

Thanks for the heads up. But, in this case, I only need to make sure the two fields have the same names, even they're mangled by my ineptitude. :D Also, that data I'm matching is mostly normal.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-04 : 15:43:51

So what happens when you do a select like I had proposed instead of putting it in the where clause? Do the names come back equal?

supernoob
Starting Member

6 Posts

Posted - 2010-03-04 : 15:45:14

I don't quite get what you meant my this:

quote:

Originally posted by DP978

Declare @t1 Table (name1 VarChar(20), name2 VarChar(20))

Insert @t1
Select 'ads''''&.23', '1/1/2--010'

Since I need to compare the same field that's in two different tables.

supernoob
Starting Member

6 Posts

Posted - 2010-03-04 : 15:47:50

quote:
Originally posted by DP978

So what happens when you do a select like I had proposed instead of putting it in the where clause? Do the names come back equal?

It seems that it returns the full set of the table...

What it essentially boils down to, is this:

SELECT DISTINCT * FROM table1, table2
  where table1.Name = table2.Name

And that I need to get the number of distinct matches between the two tables. Unless "where" already gets only distinct values...

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-04 : 15:51:27

Try something like this : Join the tables and put replace in ON

Declare @t1 Table (name1 VarChar(20))
Declare @t2 Table ( name2 VarChar(20))



Insert @t1
Select 'ads+&.23'

Insert @t2
Select  'a-ds&.23'

Select * 
From @t1 a
Inner Join @t2 b on

 Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(
             a.name1, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     '')
                        
                        =
                        
                         Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(
             b.name2, '$',     '')
                        , ')',     '')
                        , '(',     '')
                        , '@',     '')
                        , '!',     '')
                        , '#',     '')
                        , 'plus',  '')
                        , '+',     '')
                        , '/',     '')
                        , '''',    '') /* remove apostrophes */
                        , ',',     '')
                        , 'and',   '')
                        , '&',     '')
                        , '-',     '')
                        , '.',     '')
                        , ' ',     '')