• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 674
  • Last Modified:

SQL Search String for Value

I am trying to search the following string stored in a mysql database.

Agent :(c0):10/18/2007 03:01:42.239 PM r: getVersion                            0.000ms

I would like to return just the 0.000 ms part. Any suggestions?

My current query looks like:

select
      top 50 lg_trace.eventtime,
      lg_trace.comment,
      hs_clients.name
from
      lg_trace
      inner join hs_clients on hs_clients.clientid = lg_trace.clientid
where
      hs_clients.name = 'dgamdt101' and
      lg_trace.eventtime between '2007-10-18 03:00:00 PM' and '2007-10-19 06:00:00 AM' and
      lg_trace.comment like '%ms'
order by eventtime asc
0
rschoenbach
Asked:
rschoenbach
2 Solutions
 
dbbishopCommented:
select
      top 50 lg_trace.eventtime,
      lg_trace.comment,
      hs_clients.name
      substring(lg_trace.comment, patindex('%0.[0-9][0-9][0-9]%', lg_trace.comment)
from
      lg_trace
      inner join hs_clients on hs_clients.clientid = lg_trace.clientid
where
      hs_clients.name = 'dgamdt101' and
      lg_trace.eventtime between '2007-10-18 03:00:00 PM' and '2007-10-19 06:00:00 AM' and
      lg_trace.comment like '%ms'
order by eventtime asc
0
 
assystCommented:
Try this

select
      top 50 lg_trace.eventtime,
      lg_trace.comment,
      hs_clients.name,
     substring(lg_trace.comment, Len(lg_trace.comment) - charindex( ' ', Reverse(lg_trace.comment)), Len(lg_trace.comment))
from
      lg_trace
      inner join hs_clients on hs_clients.clientid = lg_trace.clientid
where
      hs_clients.name = 'dgamdt101' and
      lg_trace.eventtime between '2007-10-18 03:00:00 PM' and '2007-10-19 06:00:00 AM' and
      lg_trace.comment like '%ms'
order by eventtime asc


Or try this


select
      top 50 lg_trace.eventtime,
      lg_trace.comment,
      hs_clients.name,
     substring(lg_trace.comment, patindex('%0.[0-9][0-9][0-9]%', lg_trace.comment), len(lg_trace.comment))
from
      lg_trace
      inner join hs_clients on hs_clients.clientid = lg_trace.clientid
where
      hs_clients.name = 'dgamdt101' and
      lg_trace.eventtime between '2007-10-18 03:00:00 PM' and '2007-10-19 06:00:00 AM' and
      lg_trace.comment like '%ms'
order by eventtime asc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now