March 31, 2008 at 12:31 am
Hi the following is my resultant value.. in this i have to update the [PO]# in the LogMessage column..
RequestIdLogMessage
1PR00002N079Purchase request submitted
2PR00002N079Request fulfilled completely
3PR00002N0792 CT of [CUBICIN 500MG 10VL DSHPVIAL500MG10] processed. [PO]# P0001N079
4PR00002N079Purchase request denied
Question:
1.how can i find the [PO]# is in which rom?
2.how can i replace the existing [PO]# with new one
can you please suggest?
March 31, 2008 at 2:04 am
I'm not entirely certain on what you're asking for here but I'll give it a go.
1. To find the [PO]# in the LogMessage column you could use the charindex function and substring function, like so:
select substring(LogMessage, charindex('[PO]#',LogMessage) + 6, len(LogMessage))
This assumes various things. Namely that the actual PO number starts exactly 6 characters after the string [PO]#. And that the PO number is the last part of the string (i.e. nothing will ever follow it). It also assumes that you have a PO Number in each column. As this doesn't appear to be the case you'd have to add a where clause to your statement that restricts the returned rows to only those that contain a PO number.
where charindex('[PO]#', LogMessage)>0
As for how you replace it, use the REPLACE function. e.g.
update my_table
set LogMessage = replace(LogMessage, substring(LogMessage, charindex('[PO]#',LogMessage) + 6, len(LogMessage)) ,'12345')
where charindex('[PO]#', LogMessage) > 0
March 31, 2008 at 3:28 am
While running my query i will get 'N' num of rows, and i dont know in which rows are containing the [PO]# in the LogMessage column..
How to find where the [PO]# is available from 'N' num of rows?
How to replace that [PO]# with new [PO]#?
March 31, 2008 at 4:00 am
Ramesh (3/31/2008)
While running my query i will get 'N' num of rows, and i dont know in which rows are containing the [PO]# in the LogMessage column..How to find where the [PO]# is available from 'N' num of rows?
How to replace that [PO]# with new [PO]#?
Use the WHERE clause I gave you in my previous post to only return those rows that have [PO]#
Use the REPLACE function, as shown in my UPDATE statement, to replace the [PO]# with whatever your new [PO]# is.
By the way, don't use my code as-is. You'll need to modify it slightly to fit your particular situation - otherwise it will fail or update your table in a way you didn't want it to.
April 6, 2008 at 1:52 pm
Some unsolicited advice from my bad experience. Save the before and after image of your work and have it reviewed by someone familiar with your business systems. Data-fixes often have unintended consequences (such as changing unintended rows) that require further corrections if not tested and reviewed by _critical_unbiased_ eyes; we all have a poor record for testing our own stuff. Records of drug perscriptions can be used to deny employment or security clearances, and hacking the data with sample scripts from helpful folks should make your boss very nervous. Be totally transparant with it.
Gary
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply