We help IT Professionals succeed at work.

SQL Delimited Values

441 Views
Last Modified: 2012-05-09
I have a table that contains transaction information, including the post and response.

=======
Example:

Post:
cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123

Response:
errorfound=1&errormessage=Missing+Email

==================

My problem is that John smith might have tried to submit that 10 times.

I want to know from my log table, how many Unique people tried submitting the transaction, but got declined.

Normally I'd do a group by IPAddress if it was in separate fields, but the field contains the string post....
Comment
Watch Question

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Would like to help on thsi one, but not quite getting the problem.

We can unstring and that type of thing easy enough, but what links "cmd" to "errorfound" ?

Maybe, if you can please post a sample set of data, and what you want to see as expected results ?

That John example, we probably dont care about the 9 unseccessful times, just that the tenth did work, so, would not be considered a candidate as being declined ?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Comment out the 2nd HAVING condition if you want to count visitors even if they eventually was not declined.
declare @transactions table (id int identity, post varchar(max), response varchar(max))
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=John&lastname=Smith&ipaddress=123.123.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=Jim&lastname=Public&ipaddress=123.99.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=Jane&lastname=Doe&ipaddress=123.100.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=GI&lastname=JOE&ipaddress=123.101.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=King&lastname=Long&ipaddress=100.100.123.123', 'ok'
insert @transactions select 'cmd=process&firstname=King&lastname=Long&ipaddress=100.100.123.123', 'errorfound=1&errormessage=Already+submitted'
insert @transactions select 'cmd=process&firstname=Jerry&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=James&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=Derek&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&firstname=Tom&lastname=Brown&ipaddress=101.100.123.123', 'errorfound=1&errormessage=Missing+Email'
insert @transactions select 'cmd=process&no-params', 'errorfound=2&errormessage=Invalid+Request'
;with TMP as (
select
  ip = substring(post, nullif(patindex('%&ipaddress=%', post),0)+11, 100),
  is_error = case when response like '%errorfound=%' then 1 else 0 end   -- change the pattern to suit
from @transactions),
TMP2 as
(
select ip
from TMP
where ip is not null  -- don't care if we can't get ip
group by ip
having
   sum(is_error) > 0  -- unique visitors who have been declined
   and sum(is_error) = count(is_error)  -- count only where the user has never had a success
)
select count(*) from TMP2

-- result = 1.  IP address 101.100.123.123 is the only one that was declined and never accepted

Open in new window

Author

Commented:
@MikeToole - What do I declare @post as?

----
Current SQL
----

SELECT Right(@strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10) as ipaddress,
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/12/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 Group By Right(@strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10)

----
Current Response
----

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@strpost".

CERTIFIED EXPERT

Commented:
Sorry, I was testing on a string in the variable @post, I missed removing the first @. Just change the two occurances of @strpost to strpost


Basically it looks for the position of the string '&ipaddress' in the field strpost then takes the remaining characters from strpost starting 10 to the right.

Author

Commented:
Ok, I tried the below.  Came back with no errors, but an empty result, when I know there should be.


=========
SELECT Right(strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10) as ipaddress,
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/5/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 Group By Right(strpost, len(strpost) - CHARINDEX('&ipaddress', strpost)-10)
==========



CERTIFIED EXPERT

Commented:
So if you run this you get a count?

=========
SELECT  
 isnull(count(*),0) as result
 FROM tblLogs
 WHERE api = '1'
 AND strpost like '%crm.boostoffers.com%'
 AND strpost like '%&campaignId=28'
 AND strresponse like 'errorFound=1%'
 AND stamp >= '7/5/2010 12:00:00 AM'
 AND stamp <= '7/12/2010 11:59:59 PM'
 ==========
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi nrking83,

If you have a second would really like to get a couple of answers to those questions I posted above :)

Having the experts understanding more about your problem might make it easier to solve, and quicker to get to the bottom of it all...

Author

Commented:
Good call on that.  I was missing a character.

Just need one last thing.  See attached image.

There are still a few minor duplicates, due to the string being slightly different on the 2nd attempt
Capture.JPG
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
If all that we need is ipaddress being split out then please try :


;with ips as
(select substring(strpost, 11+CHARINDEX('&ipaddress=', strpost),CHARINDEX('&', strpost+'&', CHARINDEX('&ipaddress=', strpost)+11) - (11 + CHARINDEX('&ipaddress=', strpost)) ) as ipaddress, * FROM tblLogs)
select ipaddress, strpost
from ips


you can then do group by or whatever using the IPS cte query, e.g.

;with ips as
(select substring(strpost, 11+CHARINDEX('&ipaddress=', strpost),CHARINDEX('&', strpost+'&', CHARINDEX('&ipaddress=', strpost)+11) - (11 + CHARINDEX('&ipaddress=', strpost)) ) as ipaddress, * FROM tblLogs)
select ipaddress, count(*) as num_entries
from ips
group by ipaddress
CERTIFIED EXPERT

Commented:
@mark
Detection of the end of the IP address based on the next & was my next planned refinement.
Does the SELECT from a common table expression make any difference to the performance in this situation? - Logically it's the same as a straight select.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
CTE should be fine in this case.

Really it is just a select from a subquery, just easier to use in so much as you define the subquery at the start and it then references back to that. Lot easier to read/use when there are complex definitions to resolve in the subquery first and you want to reference those items.

When it is a simple CTE like the one above, it should be OK. It really is little more than a derived table (ie a subquery).

But there are a lot of mitigating circumstances in any query that can affect performance. Any "where" clauses should of course go inside the bracket area if possible.

But if performance is a problem, then no problems just performing the inside part of the query - it really depends on what you want to do with the results. That is partly the reason for those questions in the first post. If just extracting the ipaddress, then no advantage of a CTE over the straight query.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Goodness gracious me....

Didn't want to respond to any of my questions, didnt want to clarify a few concerns, so I posted in good faith an accurate and working solution, and still no feedback. Ouch...

Well, for what it is worth, my query does actually gives you just the ipaddress (think it is the only one that does) and you can feel free to use it, despite not wanting to acknowledge any post from me (and no, I dont mean points).

Catch ya...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.