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.
| Author |
Topic |
|
farid92691
Starting Member
3 Posts |
 Posted - 2011-08-05 : 17:08:50
|
| The following code retrieves the top 1 record with the largest number of matching characters in the same positions in @Vin against ColA1 in tableA. The objective is to get to ColB2 in tableB.. how do i do this in a less messy way? thank you!here's what's intableA:colA1 ColA2---- ----**HD*****7******* HD**A**HD*****8******* HD**B**HD**V**9******* HD**A**HD************* HD**CtableB:ColB1 ColB2 ColB3----- ----- ----- HD**A 4 1234567HD**C 2 2345678 HD**B 2 3456789Declare @Vin as varchar(17)Declare @Vin0 as varchar(17)Declare @Vin1 as varchar(17)Declare @Vin2 as varchar(17)Declare @Vin3 as varchar(17)Declare @Vin4 as varchar(17)Declare @Vin5 as varchar(17)Declare @Vin6 as varchar(17)Declare @Vin7 as varchar(17)Declare @PartNumber as varchar(19)set @Vin='KMHDG73D67U699426'set @PartNumber='1234567'Set @Vin0='**'+SUBSTRING(@Vin,3,3)+'**'+SUBSTRING(@Vin,8,1)+'*'+SUBSTRING(@Vin,10,1)+'*******'Set @Vin1='**'+SUBSTRING(@Vin,3,3)+'**'+SUBSTRING(@Vin,8,1)+'**********'Set @Vin2='**'+SUBSTRING(@Vin,3,2)+'***'+SUBSTRING(@Vin,8,1)+'*'+SUBSTRING(@Vin,10,1)+'*******'Set @Vin3='**'+SUBSTRING(@Vin,3,3)+'****'+SUBSTRING(@Vin,10,1)+'*******'Set @Vin4='**'+SUBSTRING(@Vin,3,2)+'***'+SUBSTRING(@Vin,8,1)+'*********'Set @Vin5='**'+SUBSTRING(@Vin,3,2)+'*****'+SUBSTRING(@Vin,10,1)+'*******'Set @Vin6='**'+SUBSTRING(@Vin,3,3)+'************'Set @Vin7='**'+SUBSTRING(@Vin,3,2)+'*************' SELECT top(1) ColA2,ColB2 from tableA join tableB on ColA2=ColB1 where (ColA1=@Vin0 or ColA1=@Vin1 or ColA1=@Vin2 or ColA1=@Vin3 or ColA1=@Vin4 or ColA1=@Vin5 or MDVINNo_VINCode=@Vin6 or ColA1=@Vin7) and ColB3=@PartNumber order by MDVINNo_VINCode desc |
|
|
|
|
|
|
|
Must be a better way!!