October 7, 2016 at 5:09 am
HI There,
as the heading suggests I am trying to query a text based audit for specific values then compare them values. For example the column should contain and new_ref_no and old_ref_no in the straing. I want to query for those and then compare them to see if they are in fact different. However I am not sure how I would go about doing this.
any ideas would be greatly appreciated.
Thanks
October 7, 2016 at 5:43 am
niall5098 (10/7/2016)
HI There,as the heading suggests I am trying to query a text based audit for specific values then compare them values. For example the column should contain and new_ref_no and old_ref_no in the straing. I want to query for those and then compare them to see if they are in fact different. However I am not sure how I would go about doing this.
any ideas would be greatly appreciated.
Thanks
Please provide some anonymised example data, to help us understand what you are trying to do.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 7, 2016 at 6:00 am
The easiest way is to simply put wild card values around the string you're looking for and use LIKE:
...WHERE a.Col LIKE '%new_ref_no%' AND a.Col LIKE '%old_ref_no%';
Performance will be horrible with this, but you can quickly confirm the existence of these two values if that's what you're looking for. It's an odd use case though. Can you detail more what it is that you're attempting to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2016 at 7:48 am
thanks for the reply.
the column contain a string of data. within that string I will see the following entries:
<new_pmetd_refno>4110</new_pmetd_refno>
<old_rfeco_refno></old_rfeco_refno>
so I want to do is bring back any rows that contain these values and then only show me rows where they are different.
I hope I have explained it better this time.
Thanks
October 7, 2016 at 8:00 am
niall5098 (10/7/2016)
thanks for the reply.the column contain a string of data. within that string I will see the following entries:
<new_pmetd_refno>4110</new_pmetd_refno>
<old_rfeco_refno></old_rfeco_refno>
so I want to do is bring back any rows that contain these values and then only show me rows where they are different.
I hope I have explained it better this time.
Thanks
If this column is in XML format, there may be quicker ways of performing the check.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 7, 2016 at 8:01 am
That's XML. You should be using the built-in XML functionality to shred the XML document. If you provide a more complete sample, we can help you with that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2016 at 8:04 am
HI There,
it looks like sql but is actually text
October 7, 2016 at 8:13 am
If it's not XML, and XML would be a lot easier here, then you'll have to use string manipulation to find the start and stop points using CHARINDEX. Then you can use SUBSTRING to pull the information out between the start & stop points. It's going to be a royal pain to write out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2016 at 8:22 am
thanks, unfortunately I can change the datatype.
October 7, 2016 at 8:32 am
niall5098 (10/7/2016)
HI There,it looks like sql but is actually text
XML, not SQL.
XML is text, but in structured format, with start and end 'tags' around data items.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 7, 2016 at 8:37 am
The example looks like either XML or JSON. Do you know where the data is coming from? It's fine that it's stored in a VARCHAR field, but if it's XML stored there, you have additional possibilities for how you can query the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2016 at 8:43 am
niall5098 (10/7/2016)
HI There,it looks like sql but is actually text
We said it was XML, not SQL, and XML IS text, but in a very specific format. Even if it's not XML, it contains XML and you can trick SQL into treating it as XML, which is going to be a lot easier than any other method. We would still need a sample of the data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2016 at 8:44 am
Grant Fritchey (10/7/2016)
The example looks like either XML or JSON. Do you know where the data is coming from? It's fine that it's stored in a VARCHAR field, but if it's XML stored there, you have additional possibilities for how you can query the data.
JSON uses curly brackets, so I don't think that's likely. XML is the front-runner here 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 7, 2016 at 8:46 am
apoligies, yes , XML . That was a type
October 7, 2016 at 8:50 am
Hi There,
please see example of data below:
<AUDITS>
<Admin>
</Admin>
<provider_spells>
<Refno>123456</Refno>
<Action>UPDATE</Action>
<Fields>
<old_pmetd_refno></old_pmetd_refno>
<new_pmetd_refno>4110</new_pmetd_refno>
</Fields>
</AUDITS>
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply