CODEWITHSUNDEEP
sqlsql-server
Rahul
Rahul

Reputation: 903

To join 2 views using a nvarchar column

I am trying to join 2 views using the column b and d, which are nvarchar columns. 

View 1      View 2
A | B      D | E 
w  0090    90   c
q  qw      qw  b 
z  004     4   v
l  T-Q     T-Q n

Now, have joined the columns B and D. Here since they are nvarchar columns 009 doesn't match with 9. Only records that have numbers in them have multiple 0's before the number in view 1, which do not match with the numbers in view 2 as they don't have any 0's, but the value is the same.

I want a join condition in which this matches. Converting to int is not viable as I have alphabets as well in that field.

Upvotes: 1

Views: 264

Answers (3)

SqlZim
SqlZim

Reputation: 38023

Prior to SQL Server 2012+ you could use this to replace any leading 0s:

select v1.*, v2.*
from v1
  inner join v2
    on stuff(v1.b,1,patindex('%[^0]%',v1.b)-1,'')
      = v2.d

rextester demo: http://rextester.com/CEFA74351

returns: 

+---+-----+-----+---+
| a |  b  |  d  | e |
+---+-----+-----+---+
| w | 009 | 9   | c |
| q | qw  | qw  | b |
| z | 004 | 4   | v |
| l | T-Q | T-Q | n |
+---+-----+-----+---+

The above would not match 0 and 0, one workaround replaces null values with 0. If the column can have null values then this workaround would not be appropriate as it would match existing nulls to 0.

select v1.*, v2.*
from v1
  inner join v2
    on isnull(stuff(v1.b,1,patindex('%[^0]%',v1.b)-1,''),'0')
      = v2.d

rextester demo: http://rextester.com/OTCI42982

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

Based on your example, I would use REPLACE():

SELECT *
FROM View1 V1
INNER JOIN View2 V2 on REPLACE(V1.B,'0','') = V2.D

Upvotes: 0

BJones
BJones

Reputation: 2460

Will this suit your needs? Not sure if it will cover all scenarios or not. The first ON will get INT matches, the second will get everything else that matches.

DECLARE @View1 TABLE (A NVARCHAR(10), B NVARCHAR(10))
DECLARE @View2 TABLE (D NVARCHAR(10), E NVARCHAR(10))
INSERT INTO @View1 VALUES ('w','009'),('q','qw')
INSERT INTO @View2 VALUES ('9','c'),('qw','b')

SELECT *
FROM @View1 a
JOIN @View2 b 
    ON TRY_CONVERT(INT,a.B) = TRY_CONVERT(INT,b.D)
    OR a.B = b.D

Upvotes: 3

Related Questions