Use SUBSTRING with a text variable?

Question

Post reply

Use SUBSTRING with a text variable?

Miquel Martí

Ten Centuries

Points: 1217
More actions
January 26, 2006 at 10:03 am

#170189

Hello all !!*!!
Got a very big issue, and have no idea how to solve it.
I have a very long string (>10.000) stored in a text variable (it's a long piece of code I builded dinamically)
I need to REPLACE some words in it, and then EXECUTE it
Is there any way I can do a REPLACE over it?
or simply a SUBSTRING?
You know, this typical string functions can't process anything longer than varchar(4000)

Thank you

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

lucky-80472

SSCertifiable

Points: 7793

January 26, 2006 at 10:18 am

#617246

Check "Replace" string function in BOL.

Lucky

Sergiy SSC Guru Points: 110209 More actions · Answer 2

BOL:

UPDATETEXT

Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.

Syntax

UPDATETEXT { table_name.dest_column_name dest_text_ptr }

{ NULL | insert_offset }

{ NULL | delete_length }

[ WITH LOG ]

[ inserted_data

| { table_name.src_column_name src_text_ptr } ]

_____________
Code for TallyGenerator

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 3

Thanks for your replies

The problem is there's no SUBSTRING for text

I need to EXECUTE that text string, and as long as EXECUTE cannot accept text, I would like to slice it into many varchars.

How can I slice it?

Sergiy SSC Guru Points: 110209 More actions · Answer 4

EXECUTE does accept text.

I use to run execute with 20k XML as a parameter. And it works great.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Serqiy, As you are aware, in MS-SQL Server 2000, you can't create a variable with the TEXT datatype. Where did you store the 20k XML before you used it as a parameter? In a table?

Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 6

No, I call this SP from external application.

And this application supplies whole file as a parameter.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

Ok, thanks... always a pleasure Sergiy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)