Altering text in column type text
-
Hi all
I have a column in my DB that has a column that has to be datatype TEXT as it contains more than 8000 characters.
This column is generated by a program that I have no control over. In order for a different program to read it, I need to slightly alter the text in the column. This also has to be done on a regular basis, but I know I can do this through a trigger.
My problem is I can't figure out how to read each text value and change them. If I could read the value into a var variable, this would be sooooo easy, but I can't because the fields are too long. The change I have to make is the same on every record and is at the very beginning of the field.
Please help me.
James
-
James,
A bit trickier than normal triggers because TEXT fields aren't made available in the INSERTED table, and also the problems associated with manipulating TEXT fields.
For each row in INSERTED you'll need to use the unique identifying information to set a text pointer variable to the location of the text column in the database (TEXTPTR function) and then use UPDATETEXT to manipulate the text value.
Can you post the layout of the table, a couple of actual text entries, and the changes to be made?
Cheers,
- Mark -
Thanks Mark for the suggestion
OK, the design of the column of intersest is as follows
Column Name Data Type Length Allow Nulls
WKT TEXT 16 Y
A couple truncated rows:
GEOMETRYCOLLECTION(POLYGON((143.24696 -38.701896,143.246858 -38.701941,143.246781 -38.701956,143.246573 -38.701961,143.246298 -38.701944,143.246119 -38.701913,143.245999 -38.701814,143.245427 -38.701759,143.245175 -38.701773,143.244751 -38.701756,143.244445 -38.701712,143.243952 -38.701603,143.24696 -38.701896)),POLYGON((143.245483 -38.686715,143.244477 -38.686621,143.244423 -38.686558,143.244437 -38.686544,143.245394 -38.686565,143.24554 -38.686616,143.24554 -38.686685,143.245483 -38.686715)),POLYGON((143.236124 -38.687986,143.236091 -38.688205,143.236028 -38.688235,143.235949 -38.688234,143.23591 -38.68819,143.235898 -38.688137,143.235909 -38.688075,143.235943 -38.688018,143.235994 -38.687983,143.236119 -38.687988,143.236124 -38.687986)))
GEOMETRYCOLLECTION(POLYGON((143.237106 -38.694878,143.237152 -38.694624,143.237201 -38.694657,143.237282 -38.694738,143.237285 -38.694744,143.237291 -38.69476,143.237309 -38.694796,143.237329 -38.694833,143.237352 -38.694868,143.237364 -38.694884,143.237384 -38.694908,143.237396 -38.694957,143.237561 -38.695745,143.238838 -38.697241,143.238848 -38.697262,143.238851 -
Most of the columns show up in Enteprise as <Long Text> but I know that they have the same structure.
What I want to do is to change delete all occasions of POLYGON and to change GEOMETRYCOLLECTION to MULTIPOLYGON. If I could read into a variable @wkt it would be
REPLACE(@wkt,'POLYGON','') and REPLACE(@wkt,'GEOMETRYCOLLECTION','MULTIPOLYGON')
TIA
James
-
Hi James,
I don't know if this is useful, but this example assumes a PK (or UNIQUE identifier) called "id" on the table....
create trigger tr_MyTable on MyTable for insert, update as if not update(WKT) goto skipit
declare @id int, @textptr varbinary(16) while 1=1 begin select top 1 @id = id from inserted where @id is null or id > @id order by id if @@rowcount < 1 break
select 'BEFORE: ', * from MyTable where id = @id
select @textptr = textptr(WKT) from MyTable where id = @id and WKT like 'GEOMETRYCOLLECTION(POLYGON%' if @@rowcount > 0 updatetext MyTable.WKT @textptr 0 26 'MULTIPOLYGON('
select 'AFTER : ', * from MyTable where id = @id end
skipit: go
Cheers,
- Mark -
Mark
You post was mighty helpful, just about did everything I every wanted. The only other thing was replacing the POLYGON text that pops up multiple times in each text string. I am not all that familiar with text cursors, so if I was to use the following before you select @textpt=textptr(WKT) block, then would the code below firstly remove all the intances of POLYGON within the text column? Would it perhaps be wise to use a different textptr variable?
select @textptr = textptr(WKT)
from MyTable
where id = @id
and WKT like '%POLYGON%'
if @@rowcount > 0
updatetext MyTable.WKT @textptr 0 7 ''
Your help is most appreciated
Cheers
James
-
hmmm, sorry, I missed the fact that you mentioned ALL occurences of POLYGON. It gets a bit more complicated, and the following code won't win you any points for "high performance T-SQL of the Year", but it will do what you want (I think) plus avoid replacing the POLYGON in MULTIPOLYGON should an update occur.
alter trigger tr_MyTable
on MyTable
for insert, update
as
if not update(WKT) goto skipit
declare @id int, @textptr varbinary(16), @patternPtr int
while 1=1
begin
select top 1 @id = id
from inserted
where @id is null or id > @id
order by id
if @@rowcount < 1 break
select 'BEFORE: ', * from MyTable where id = @id
while 1=1
begin
select
@textptr = textptr(WKT),
@patternPtr = patindex('%[^I]POLYGON%',WKT)
from MyTable
where id = @id
if @patternPtr < 1 break
updatetext MyTable.WKT @textptr @patternPtr 7
end
while 1=1
begin
select
@textptr = textptr(WKT),
@patternPtr = patindex('GEOMETRYCOLLECTION%',WKT)-1
from MyTable
where id = @id
if @patternPtr < 0 break
updatetext MyTable.WKT @textptr @patternPtr 18 'MULTIPOLYGON'
end
select 'AFTER : ', * from MyTable where id = @id
end
skipit:
Cheers,
- Mark -
Thanks Mark, I will let you know how I go. By the way, I am not that worried about good T-SQL, as long as it works for now. Efficiency comes later.
-
OK. I have been playing with this trigger for a while now, and all seemed to be going fine. That was until I needed to apply the change to a text field that had (I believe) more than 8000 characters.
When the field to run the seach and replace on is very long, it won't run the updatetext line. Any ideas?
TIA
James
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply