How to find a string?

I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
column Msgtest have ca 800 000 rows.
One ex. on some rows on Msgtext column is:
-Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
Domain:
-Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
I need a query that can find and cut just string 'User Name:' but with the
actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:'
without actuell user name, jump over. The data in these rows is space
delimited.
Then the query should check if the ex. 'User Name:Rolf' is found more than 3
times
within last 24 hours, then create a table caled 'Result' and put in the
actuell user
name i the table 'Result' under column 'User'.
Thanks!Hi Mile
You can check PATINDEX function avaliable for Strings in SQL Server
http://msdn.microsoft.com/library/d...br />
28xk.asp
once you have the position u can use
http://msdn.microsoft.com/library/e...s.blogspot.com/
http://www.SQLResource.com/
---
"Mile" wrote:

> I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
> column Msgtest have ca 800 000 rows.
> One ex. on some rows on Msgtext column is:
> -Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
> Domain:
> -Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
> I need a query that can find and cut just string 'User Name:' but with the
> actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:
'
> without actuell user name, jump over. The data in these rows is space
> delimited.
> Then the query should check if the ex. 'User Name:Rolf' is found more than
3
> times
> within last 24 hours, then create a table caled 'Result' and put in the
> actuell user
> name i the table 'Result' under column 'User'.
> Thanks!|||Thanks Chandra,
I'm not so familiar with transact T-SQL code, I don't understand how to cut
after ex.
'User Name:Rolf' because the actuell user (in this case Rolf) can variety in
character length.
Can you please tray to write code for my example?
Thanks!
"Chandra" wrote:
> Hi Mile
> You can check PATINDEX function avaliable for Strings in SQL Server
> http://msdn.microsoft.com/library/d... />
z_28xk.asp
> once you have the position u can use
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Mile" wrote:
>|||Hi Mile
Just check this one. It might help you
SELECT
CASE WHEN PATINDEX(''User Name:%',user) > 1 THEN
SUBSTRING(user, PATINDEX(''User Name:%',user), LEN(user) - PATINDEX(''User
Name:%',user) )
ELSE user
END
[NAME]
FROM RESULT
Please not that it is just a sample and to give u an example. I didnt try
and execute it before sending.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mile" wrote:
> Thanks Chandra,
> I'm not so familiar with transact T-SQL code, I don't understand how to cu
t
> after ex.
> 'User Name:Rolf' because the actuell user (in this case Rolf) can variety
in
> character length.
> Can you please tray to write code for my example?
> Thanks!
> "Chandra" wrote:
>|||Thanks Chandra, I have tested this code and get the 'User Name:' (with
actuell user)
in a temporerly table caled 'Result':
select substring(msgtext,charindex('User Name:',msgtext),
charindex(' ',msgtext,charindex('User Name:',msgtext)+len('User
Name'))-charindex('User Name:',msgtext)) 'Result' from syslogd where msgtext
like '%User Name:%' and msgtext not like '%User Name: %'
Can you help me now to put all these 'User Name:' that is repeated more than
3 times
in a permanent table caled 'Finish'.
Thanks in advance!
"Chandra" skrev:
> Hi Mile
> Just check this one. It might help you
> SELECT
> CASE WHEN PATINDEX(''User Name:%',user) > 1 THEN
> SUBSTRING(user, PATINDEX(''User Name:%',user), LEN(user) - PATINDEX(''Us
er
> Name:%',user) )
> ELSE user
> END
> [NAME]
> FROM RESULT
>
> Please not that it is just a sample and to give u an example. I didnt try
> and execute it before sending.
> Please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Mile" wrote:
>|||On Tue, 25 Oct 2005 05:40:09 -0700, Mile wrote:

>I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
>column Msgtest have ca 800 000 rows.
>One ex. on some rows on Msgtext column is:
>-Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
>Domain:
>-Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
>I need a query that can find and cut just string 'User Name:' but with the
>actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:'
>without actuell user name, jump over. The data in these rows is space
>delimited.
Hi Mile,
For this first part, try if this suits your needs:
SELECT SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
FROM Test
WHERE Msgtext LIKE '%User Name:[^ ]%'

>Then the query should check if the ex. 'User Name:Rolf' is found more than
3
>times
>within last 24 hours, then create a table caled 'Result' and put in the
>actuell user
>name i the table 'Result' under column 'User'.
>Thanks!
Assuming the above query works as expected, you can now change it to
SELECT SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
FROM Test
WHERE Msgtext LIKE '%User Name:[^ ]%'
GROUP BY SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
HAVING COUNT(*) > 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo, it works!!!
"Hugo Kornelis" wrote:

> On Tue, 25 Oct 2005 05:40:09 -0700, Mile wrote:
>
> Hi Mile,
> For this first part, try if this suits your needs:
> SELECT SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> FROM Test
> WHERE Msgtext LIKE '%User Name:[^ ]%'
>
> Assuming the above query works as expected, you can now change it to
> SELECT SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> FROM Test
> WHERE Msgtext LIKE '%User Name:[^ ]%'
> GROUP BY SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> HAVING COUNT(*) > 3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>