parse a varchar(2000) - SQL Server Forums

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

Transact-SQL (2000)

parse a varchar(2000)

Author

Topic

gertbelien
Starting Member

9 Posts

Posted - 2006-02-09 : 14:23:27

i have a field xmes varchar(2000) in table Persons with the following content ;

[forwarding message]
[internal forwarded]
re: problem with printer

i need to write trigger that put's the text 're: problem with printer'
into a field "Description" of table "Messages" every time a record is inserted into Persons.
So i only need the last line.

How do i write this trigger ???

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-09 : 14:38:07

U may use the Logic : Find the charindex of last char(13) and get the right of that

gertbelien
Starting Member

9 Posts

Posted - 2006-02-09 : 14:41:59

quote:
Originally posted by Srinika

U may use the Logic : Find the charindex of last char(13) and get the right of that

thanks for the answer, but i am completely new to SQL Server.
Do you have a completye example how to write the trigger ?
thx

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 14:42:22

Does it always start "re:" - or is it the last thing after the last CHAR(13)+CHAR(10) in the field? or some other criteria to establish where it starts?

Kristen

gertbelien
Starting Member

9 Posts

Posted - 2006-02-09 : 14:45:12

quote:
Originally posted by Kristen

Does it always start "re:" - or is it the last thing after the last CHAR(13)+CHAR(10) in the field? or some other criteria to establish where it starts?

Kristen

yes, i need the last line after CHAR(13)+CHAR(10).
It doesn't allways start with Re :
i need a complete Trigger example because i'm a newbie in SQL Server
(i'm an oracle geak)

gertbelien
Starting Member

9 Posts

Posted - 2006-02-09 : 14:48:26

i will reply to tomorrow. I have a meeting now.
thanks in advance for all suggestions.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-09 : 15:16:05

The following will work if there are no 2 enter keys in the end of text

Select reverse(left(reverse(MultiLineField),charindex(char(13),reverse(MultiLineField)))) from urtbl

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 15:19:14

Here is a complete trigger example as well if that is what you need. (Books Online also contains complete examples)

This assumes you want the entire line if there is only 1 line.


set nocount on
use pubs
create table junk (rowid int primary key, v varchar(2000))
create table junk2 (rowid int, v varchar(2000))
go

create trigger tr_junk_ins on junk after insert
as
begin
	insert junk2 (rowid, v)
	select	i.rowid
		,case
			when charindex(char(13), i.v) > 0 
			then right(i.v, charindex(char(13), reverse(i.v))-2)
			else i.v
		end
	from	inserted i
end
go

insert junk (rowid, v)
select 1, '[forwarding message]
[internal forwarded]
re: problem with printer'

insert junk (rowid, v)
select 2, '[forwarding message]'

go
print 'junk'
select * from junk

print '
junk2'
select * from junk2

go
drop table junk
drop table junk2

Be One with the Optimizer
TG

gertbelien
Starting Member

9 Posts

Posted - 2006-02-10 : 04:50:34

I tried to create the following :

CREATE TRIGGER Create_Email_Customer ON [dbo].[CL_CALL_LOGGING]
FOR INSERT
AS

declare
@sProblem_Desc VARCHAR(2000),
@sSolution_Desc VARCHAR(2000),
@sResult_Desc VARCHAR(2000)

BEGIN
SELECT
@sProblem_Desc = CONVERT(VARCHAR(2000),PROBLEM_DESC),
@sSolution_Desc = CONVERT(VARCHAR(2000),SOLUTION_DESC)
FROM inserted

IF SUBSTRING (@sProblem_Desc, 1, 22) = '[Call Logged via Email'
IF charindex(char(13), @sSolution_Desc) > 0
select @sResult_Desc = right(@sSolution_Desc, charindex(char(13), reverse(@sSolution_Desc))-2) + @sProblem_Desc
else select @sResult_Desc = @sSolution_Desc + @sProblem_Desc

update CL_CALL_LOGGING
SET PROBLEM_DESC = @sResult_Desc
WHERE CALL_NUMBER = i.CALL_NUMBER
END

When i execute this i'll get the following error :
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
The column prefix 'i' does not match with a table name or alias name used in the query.

ANY IDEA's ????

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-10 : 06:01:26

see this..
http://sqlteam.com/forums/topic.asp?TOPIC_ID=61537

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:17:25

shallu1_gupta: Can I just rephrase that for others coming along?

The thread has continued over on that thread, rather than that being a possible solution!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources