CODEWITHSUNDEEP
sqlsql-server
dopplesoldner
dopplesoldner

Reputation: 9479

SQL Nested Replace

I have a complex nested replace which I am using to join two tables in MSSQL.

select * from A
  left outer join 
select * from B
on
  replace(
     replace(
        replace(
           replace(
              replace(A.Column1, '1114', ''),
             '1160', ''), 
          '1162', ''),
        '1167', ''),
      '1176', ''),
    '1177', '')  = B.Column1

The whole reason I am doing this is because data in Table1 contains of some noise - numbers like 1160, 1162 etc wheres Table2 is clean characters.

Eg. - Table 1 - 'HELLO1160WORLD'
      Table 2 - 'HELLOWORLD'

Now in my situation I should be able to match them as one entry.

My current approach of nested replace does work but I am not convinced that this is an elegant way to do this. Any help will be much appreciated. Thanks

Upvotes: 5

Views: 25582

Answers (3)

usr
usr

Reputation: 171206

The problem is that T-SQL does not easily allow to mark expressions with a name so you can refer to them from a different place. There is a way to do this though:

select replaceN
from T
cross apply (select replace1 = replace(T.col, 'x', 'y')) r1
cross apply (select replace2 = replace(replace1, 'x', 'y')) r2
cross apply (select replace3 = replace(replace2, 'x', 'y')) r3
...

This at least gets rid of the crazy nesting. It has no negative performance impact.

Upvotes: 4

PowerUser
PowerUser

Reputation: 11791

Bluefeet's suggestion would do definitely do a good job of making your query much simpler. However, if you don't want to bother with a function and keep all your code one place, try this. Before you do the join, you could dump table A into a staging table:

DECLARE @TmpA TABLE(
     Column1 [nvarchar] (50)),
     ...
     )

Insert into @tmpA select * from A

Update @TmpA set Column1=Replace(Replace(Replace(Column1,...)))

Select * from tmpA
  left outer join 
Select * from B
  on tmpA.Column1=B.Column1

Upvotes: 0

Taryn
Taryn

Reputation: 247810

Maybe use a function to strip the non-numeric characters:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    While PatIndex('%[^a-z]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

    Return @Temp
End

Then you will reference this function in your join:

select a.col1 a, b.col1 b
from tablea a
left join tableb b
  on dbo.RemoveNonAlphaCharacters(a.col1) = b.col1

See SQL Fiddle with Demo

Upvotes: 4

Related Questions