Searching Text in field (type ntext)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching Text in field (type ntext)

Author

Topic

stevo_3
Starting Member

20 Posts

Posted - 2006-01-17 : 10:30:54

Hello,

i got a table workitem with fields
id(key)
comment nchar 16
name1
name2

when adding a record from a client application he adds this in comment:
<STIB> 0502R314231 <LOCATION> STIB TOR 15 3e av. <FIRSTNAME> NATHALIE
<LASTNAME> HENRY

<FIRSTNAME>,<NAME> are always constant
what follows here: NATHALIE or HENRY can be different :)

What i want is a trigger before insert or update, that populates my fields name1 and name2 with the right information from the comment.
sow in the field name1 he has to put HENRY for this example
in the field name2 he has to put NATHALIE

in fact he needs to search for the string <FIRSTNAME> evrything that follows till the next "<"-sign he needs to save and put in the right field wich is name1
the same for name 2

Does anyone has an idea how to do this in SQL,

Thx

Steve

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-17 : 11:33:40

U want to extract the 2 names from an XML type text and insert in to a table ?
in BOL
- Check for String functions in T-SQL (Like CharIndex, Substring ...)
- How to Write a function to accept a parameter & output 2 elements

Write the function with above 2 to take comment field contents as parameter and return the 2 names from that

(I'm not sure whether the parameters of functions are allowed with ntext field type, so check on that as well)

Write SQL to insert into the necessary table with the use of Function in the select statement

stevo_3
Starting Member

20 Posts

Posted - 2006-01-18 : 04:57:18

Hello,

the charindex function is really powerful thing but i got an error message when i try to execute the folowwing code in a trigger:

CREATE TRIGGER [stibrequests] ON [dbo].[workitem]
FOR INSERT, UPDATE
AS

update workitem
SET contactL = substring(comment,
charindex('<LASTNAME>',comment)+11 ,
charindex('<LANGUAGE>',comment)-13 - charindex('<LASTNAME>',comment))

update workitem
SET contactF = substring(comment,
charindex('<FIRSTNAME>',comment)+12 ,
charindex('<LASTNAME>',comment)-13 - charindex('<FIRSTNAME>',comment))

Error message:
Description: An unexpected error has occurred. Invalid length parameter passed to the substring function.
Invalid length parameter passed to the substring function.
The statement has been terminated

When i execute this in SQL analyzer whitout trigger it's OK,
strange :(

Anyone has an idea what i can do about that, it is absolutely necessary that i use this code in a trigger

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-18 : 10:55:14

May be u need to use the inserted, deleted ... to represent the table where the triggering is performed.

Anyway check with a simple Substring( hard coded text), CharIndex(hard coded index) , A varchar field (instead of ntext type comment field) and c the results

eg1. : update workitem SET contactL = substring(FieldWithVarcharType,2,4)
eg2. : update workitem SET contactL = substring(FieldWithVarcharType,charindex('My text to be searched',FieldWithVarcharType ),4)
eg3. : update workitem SET contactL = substring(Field nTextType,charindex('My text to be searched',FieldWithVarcharType ),4)

do experiments like that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources