September 15, 2006 at 12:56 am
I need some help with altering the text held within a text field. I am not all that confident on cursors, let along what is needed for ones that deal with text fields over 8000 characters in length.
I have tables with varying amount of rows (some a few million) that all need text modified in one text field. If anyone is familiar, the text is storing shape geometry. There are 3 things that I need to change:
The first text string (for a multi polygon)
GEOMETRYCOLLECTION(POLYGON((143.275376 -38.729836,143.279852 -38.725814,143.282226 -38.730841,143.275376 -38.729836)),POLYGON((143.282972 -38.730629,143.280734 -38.725762,143.285753 -38.726185,143.282972 -38.730629)))
This is a simple case, more often than not, these are over 8000 characters long. This needs to remove every occurance of the word POLYGON. Once that is gone, every occurance of "((" must be changed to "(" and every occurance of "))" must be changed to ")". Then finally GEOMETRYCOLLECTION must be changed to MULTIPOLYGON.
The second string (for a multi polyline)
GEOMETRYCOLLECTION(LINESTRING((143.275376 -38.729836,143.279852 -38.725814,143.282226 -38.730841,143.275376 -38.729836)),LINESTRING((143.282972 -38.730629,143.280734 -38.725762,143.285753 -38.726185,143.282972 -38.730629)))
Very similar to the first case, except LINESTRING must be removed and GEOMETRYCOLLECTION changed to MULTILINESTRING, the changing of the brackets still applies.
The third text string (for points)
GEOMETRYCOLLECTION(POINT(143.282361 -38.724545),POINT(143.287652 -38.72518))
Again is similar, POINT must be removed and brackets modified as above and GEOMETRYCOLLECTION changed to POINT.
The column that contains this information can contain any of the three options above (although each record can only contain one of either LINESTRING, POINT, or POLYGON).
If anyone has some code that would do this, I would be most appreciative.
Cheers
James
September 15, 2006 at 1:36 am
BLOB fields (and TEXT as a case of DLOB) are not a part of Relational Data model, so it not to be managed or processed by RDMS (and SQL Server as a case od RDMS).
If you really mean to work in SQL Server (which is RDMS, not a text editor) you must read the text, convert it into data (which means dismanle source flow into smallest pieces, identify entities and sort it out) and place it into tables having proper relations established.
Than you may generate a report from that data sets in whatever format you prefer.
_____________
Code for TallyGenerator
September 15, 2006 at 2:51 am
James
Sergiy is right - you should look at normalising your database by splitting distinct data from your text field into separate columns. However, to answer your question... look up the UPDATETEXT and PATINDEX statements in Books Online.
John
September 15, 2006 at 4:04 am
Probably you could do it with UPDATETEXT and PATINDEX.
But, honestly, it's easier to do it with simple macro in MS Word.
_____________
Code for TallyGenerator
September 17, 2006 at 6:32 pm
I am aware that what I am trying to do is not ideal, but I don't have too many other options. The only reason I am using this text field, is as a conversion from another blob field. The format in that blob field is not compatible with some other software I am using, and the format is not published. The only way I can get these two pieces of sofware happily read the blob data is to use one to convert to text, then alter the text, then convert back to blob. Frustrating, but it is the only way I think I can go, as there are constraints with the original blob datatype.
I know it can be done with UPDATETEXT and PATINDEX, but I don't really understand how these work, so if I could get a hand it would be most appeciated.
Cheers James
September 17, 2006 at 7:24 pm
If you don't know structure of your text you cannot build a script to modify it.
If you know enough to modify the text than you can dismantle it into pieces, work them out and build the output string from it.
If you mean to replace "((" with "(" than "((" is actually your field separator. Same for "))"
Don't be lazy, do it proper way, it will save you a lot of time.
_____________
Code for TallyGenerator
September 18, 2006 at 1:35 am
OK. Now I think I understand where you are coming from. I was am not trying to be lazy, I just didn't fully understand what you were saying until you outlined the approach. I will give it a shot and see how I go.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply