Suppose a table ABC have column XYZ containing data

3PSY0001-06-08-F
3PSY0001A-07-09-P
3PSY0001-02-09-P
3PSY0001-07-07-F
3PSY0002-09-08-P
3PSY0002A-01-03-P
3PSY0002-07-07-F

How can i write a query so that the data ending with P without A can be updated with A.

For example, with that query, data

3PSY0001-02-09-P AND 3PSY0002-09-08-P will be updated as

3PSY0001A-02-09-P AND 3PSY0002A-09-08-P while the other data remains the same.

Please Help.

Warm Regards

update ABC
  set XYZ = left(XYZ, 8) + 'A' + substring(XYZ, 9, 1000)
  where XYZ like '%P' and XYZ not like '%A%'

Hi Huangzhi,

Works like a charm. You are a life saver. Thanks a lot.

Warm Regards

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.