text field that contains varying strings
-
I Need to get only specific data from a text field that contains varying strings
for example,
a text field in a table contains the following data:
Logon Failure: Reason: Unknown user name or bad password User Name: clare morgan Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO
so as you can see very messy
the data i need is CLAREMORGAN part of this text field
how can i retrieve only all the user names and nothing else ?
i am using sql 2000
thanking you in advance
-
I thinks something like this might work. Let me know if this does the trick:
create table x (row int, test_text text)
insert into x values (1,'Logon Failure: Reason: Unknown user name or bad password User Name: clare morgan Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO')
insert into x values (2,'Logon Failure: Reason: Unknown user name or bad password User Name: dick clark Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO')
insert into x values (3,'Logon Failure: Reason: Unknown user name or bad password User Name: joe smith Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO')
insert into x values (4,'Logon Failure: Reason: Unknown user name or bad password User Name: greg larsen Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO')
insert into x values (5,'Logon Failure: Reason: Unknown user name or bad password User Name: will johnson Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advapi Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Workstation Name: LO')
select * from x
declare @x int
declare @y int
declare @r int
declare @t int
declare @cmd nvarchar(1000)
DECLARE @ptrval binary(16)
set @r = 1
select @t=count(*) from x
while @r <= @t
begin
set @cmd = 'SELECT top ' + cast(@r as char) + ' @x=patindex(''%User Name: %'',test_text)+10 FROM X'
exec sp_executesql @cmd,N'@x int out',@x out
set @cmd = 'SELECT top ' + cast(@r as char) + ' @y=patindex(''%Domain: %'',test_text)-2 FROM X'
exec sp_executesql @cmd,N'@y int out',@y out
set @y = @y-@x+1
set @cmd = 'select top ' + cast(@r as char) + ' @ptrval = TEXTPTR(test_text) from x ' + char(13) +
'readtext x.test_text @ptrval ' + cast(@x as char) + ' ' + cast(@y as char)
exec sp_executesql @cmd,N'@ptrval binary(16)',@ptrval = 1
end
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
How about
select SUBSTRING(test_text,CHARINDEX('User Name: ', test_text)+11,CHARINDEX(' Domain:', test_text)-CHARINDEX('User Name: ', test_text)-11)
from x
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
How aboutselect SUBSTRING(test_text,CHARINDEX('User Name: ', test_text)+11,CHARINDEX(' Domain:', test_text)-CHARINDEX('User Name: ', test_text)-11)
from x
thanks a million for that
but i'm coming back with an error message
'Invalid length parameter passed to the substring function.'
-
My answer depends on each row of text having both 'User Name:' and 'Domain:', also if there is no name will give you the error.
Try
select LTRIM(RTRIM(SUBSTRING(test_text,CHARINDEX('User Name:', test_text)+10,CHARINDEX('Domain:', test_text)-CHARINDEX('User Name: ', test_text)-10)))
from x
but you will get blank results where there is no name.
Far away is close at hand in the images of elsewhere.
Anon. -
that results in showing the following couple of sample rows:
shaunpsu Domain: test Logon Type: 2 Logon Process: Advapi
PaulBa Domain: test Logon Type: 2 Logon Process: User32
which is part of the way
so now i need to grab
shaunpsu
PaulBa
and nothing after the first space
is this possible ?
-
When I use the code with your sample data and Greg's it works for me!
Is there something different about the data?
Can u post small sample?
Far away is close at hand in the images of elsewhere.
Anon. -
when i execute the select statement in sql query it comes back with result set i've shown you but also with the error
'Invalid length parameter passed to the substring function.'
random sample result set is as follows:
claremsu Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advap
rosinaa Domain: LONTTMIG01 Logon Type: 2 Logon Process: Advapi
adminstrator Domain: LONCITIMAGE02 Logon Type: 2 Logon Process: User32
peterw Domain: SYMBIAN
martinco Domain: SYMBIAN Lo
is this enough to go on??
-
Bit stumped really.
I'm using SQL7 so don't know if that is the problem!!.
In the first sample you posted is all the data in one text field or is it split?
Can u post the whole contents of the text file of the record that is failing.
Use the following to show the substring data and see if any problems
select CHARINDEX('User Name: ', test_text)+11,CHARINDEX(' Domain:', test_text)-CHARINDEX('User Name: ', test_text)-11
from x
Far away is close at hand in the images of elsewhere.
Anon. -
Maybe something like this might work for you. This should return one word proceeding the characters "Domain:"
create table x (row int, test_text text)
insert into x values (1,'User name: claremsu Domain: LONPSGNOTESDEV0 Logon Type: 2 Logon Process: Advap')
insert into x values (2,'Some other text peterw Domain: SYMBIAN ')
insert into x values (3,'rosinaa Domain: LONTTMIG01 Logon Type: 2 Logon Process: Advapi ')
insert into x values (4,'adminstrator Domain: LONCITIMAGE02 Logon Type: 2 Logon Process: User32 ')
insert into x values (5,'peterw Domain: SYMBIAN ')
select ltrim(reverse(substring(ltrim(reverse(substring(test_text,1,CHARINDEX('Domain:', test_text)-1))),1,
case when charindex(' ',ltrim(reverse(substring(test_text,1,CHARINDEX('Domain:', test_text)-1)))) = 0
then len(ltrim(reverse(substring(test_text,1,CHARINDEX('Domain:', test_text)-1))))
else charindex(' ',ltrim(reverse(substring(test_text,1,CHARINDEX('Domain:', test_text)-1))))
end
)))
from x
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply