x

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.
more ▼

asked Sep 04 '12 at 07:01 PM in Default

shalabh21 gravatar image

shalabh21
51 8 11 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Sep 04 '12 at 07:21 PM

DBA_Cabuloso gravatar image

DBA_Cabuloso
10

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.
Sep 04 '12 at 08:08 PM shalabh21
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x128
x11

asked: Sep 04 '12 at 07:01 PM

Seen: 474 times

Last Updated: Sep 04 '12 at 08:09 PM