asked
Sql Update Statement.
Hi All, I need to write on sql update statement with following requirement. Lets I have one string value in column C1 of table A. C1 = 'AA=1,BB2=8,KL=8,NA=6,A1=2,B1=2,C1=2' focus on values string part A1=2,B1=2,C1=2. one thing the position of sub string A1=2,B1=2,C1=2 in field C1 is may vary. I have to find that sub string from field C1 of table A and replace it with blank value. Therefore my original String 'AA=1,BB2=8,KL=8,NA=6,A1=2,B1=2,C1=2' will change to 'AA=1,BB2=8,KL=8,NA=6' and then this value needs to update the column C1 of table A. i.e. before Update C1 = 'AA=1,BB2=8,KL=8,NA=6,A1=2,B1=2,C1=2' After Update C1 'AA=1,BB2=8,KL=8,NA=6' One thing which I have not mentioned that the string pattern may be like this C1 = 'AA=1,BB2=8,KL=8,NA=6,A1=2,B1=2,C1=2' C1 = 'AA=1,BB2=8,KL=8,A1=2,NA=6,B1=2,C1=2' C1 = 'AA=1,BB2=8,B1=2,KL=8,NA=6,A1=2,C1=2' C1 = 'AA=1,C1=2,BB2=8,KL=8,NA=6,A1=2,B1=2' i.e there is no fix pattern of main string. I have to find these part A1=2,B1=2,C1=2 from original string and needs to be update by blank value. i.e. final result should exclude A1=2,B1=2,C1=2 In update statement we also need to take care of comma. Please help as this quite urgent for. Thank you in advance.
First you have to find the pattern you want. Use CHARINDEX to do this, or PATINDEX if you want to use wildcard characters. Later, you select only the part of the string before the pattern you want. So would be something like this: UPDATE A SET C1 = SUBSTRING(C1, 1, CHARINDEX('', C1) It is easy. But you should save each equality in one row or field. Look for the function fnSplit in the internet, and split these values with the comma. Best Regards, Lucas Benevides DBA Cabuloso
Thank you Lucas. One thing which I have not mentioned that the string pattern may be like this C1 = 'AA=1,BB2=8,KL=8,NA=6,A1=2,B1=2,C1=2' C1 = 'AA=1,BB2=8,KL=8,A1=2,NA=6,B1=2,C1=2' C1 = 'AA=1,BB2=8,B1=2,KL=8,NA=6,A1=2,C1=2' C1 = 'AA=1,C1=2,BB2=8,KL=8,NA=6,A1=2,B1=2' i.e there is no fix pattern of main string. I have to find these part A1=2,B1=2,C1=2 from original string and needs to be update by blank value. i.e. final result should exclude A1=2,B1=2,C1=2 Regards, Rajat.
8 People are following this question.
