September 6, 2013 at 4:19 am
I have a table called T1 and column called C1.It contains one code like TCR-ABCDE12345.
I have another table T2 ,which has 4 columns and its values as shown below.
C1 C2 C3 C4
ABCD 12345 PQRST 98765
What I need to get from T1 ,when I query as below is
Select C1 from T1
Query Result : TCR-PQRST98765
ie select SUBSTRING(C1,5,5) from T1 - Will be replaced with its corresponding value from T2 (ABCD will be replaced with PQRST)
i.e.from fifth character till 10th of C1 of T1 needs to be replaced with its corresponding value C3 from T2
select SUBSTRING(C1,10,len(C1)) from T1 Will be replaced with its corresponding value from T2 (12345 will be replaced with 98765)
i.e.from 10 th character of C1 of T1 needs to be replaced with its corresponding value C4 from T2.
September 6, 2013 at 5:20 am
Please give us the DDL statements (CREATE TABLE) and some sample data (preferrably multiple rows) for each table. Tell us the expected results for the samples provided.
I also don't read in you post how the tables are joined togeteher. How will you know which row of T1 matches with which row in T2.
September 6, 2013 at 9:27 am
I think the REPLACE function will do what you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2013 at 5:24 am
Table1
Create table T1
C1 varchar(50)
Insert into T1 Values ('TCR-12345ABCDEF')
GO
Insert into T1 Values('TCR-13245HIJKLM')
GO
Insert into T1 Values('TCR-14567RSTUV')
Table2
Create Table T2
(C1 varchar (50),
C2 varchat (50),
C3 varchar(50),
C4 varchar(50)
)
insert into T2 values('12345','ABCDEF','98765','PQRST')
GO
insert into T2 values('13245','HIJKLM','45678','VWXYZ')
expected output after we apply the update query to T1.I am here looking for the "update query" to T1.
select * from T1
TCR-98765PQRST
TCR-45678VWXYZ
TCR-14567RSTUV
It means 5th character to 10th character of C1 of T1 should be replaced by C3 of T2,when 5th to 10th charecter of C1 of T1 matches with the C1 of Similarity
10th character to the last character of C1 of T1 should be replaced by C4 of T2,when 10th to last character of C1 of T1 matches with the C2 of T2.
Hence the third row from T1 is unchanged as there is no matching record from T2.
September 8, 2013 at 3:30 pm
Use the stuff function, wrapped in a case expression that tells you whether both bits match or just the first or just the second.
Before you use this code, read and understand the Books Online Pages on CASE, Common Table Expressions, and STUFF; and then check that you understand what the code is doing. That it works on your tiny data sample is no guarantee that it will work correctly on other data.
;WITH cte AS (select T1.C1 as C0, T2.C1, T2.C2, T2.C3, T2.C4
from T1, T2
where T2.C1 = substring(T1.C1,5,5) and T2.C2=substring(T1.C1,10,len(T2.C2))
)
UPDATE cte set C0 =
case when C1 = substring(C0,5,5) and C2=substring(C0,10,len(C2))
then stuff(C0,5,len(C0),C3+C4)
when C1 = substring(C0,5,len(C1))
then stuff(C0,5,5,C3)
else stuff(C0,10,len(C0),C4)
end
select * from T1
The code above lets C2 and C4 be any length, since sometimes they are 5 and sometimes 6 in your sample data, but assumes T2.C1 and C3 are always 5 characters, as in the sample data; also, it assumes that you may want to change both or either of the field in T1 corresponding to columns C3 and C4 in T2; so you may need to change it.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply