Home > Archive > SQL Server Programming > October 2005 > How to find a string?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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!
| |
| Chandra 2005-10-25, 7:04 pm |
| Hi Mile
You can check PATINDEX function avaliable for Strings in SQL Server
http://msdn.microsoft.com/library/d..._pa-pz_28xk.asp
once you have the position u can use
http://msdn.microsoft.com/library/e....asp?frame=true
Please let me know if this helped? do revert back if u have any questions
--
best Regards,
Chandra
http://chanduas.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!
| |
|
|
| Chandra 2005-10-26, 4:02 am |
| 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:
[color=darkred]
> 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:
>
| |
|
| 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:
[color=darkred]
> 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:
>
| |
| Hugo Kornelis 2005-10-27, 7:03 pm |
| 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)
>
|
|
|
|
|