Replace-type function for Text datatype

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?Zack Sessions (zcsessions@.visionair.com) writes:
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?

One way would be to iterate over the table, and for each row get slices
of 8000 chars to a varchar value on which you run replace(). You would
then use updatetext to update the row. A bit tricky, because if first
got chars 1 to 8000, and removed 6 char(15), you should now start on
char 7994 for the next batch.

Not particularly funny, I know.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hello:

You could write a small vbscript that would loop thru the table and
update the text columns using ado's appendchunk method and the replace
function in vbscript.

See link below on an example that you can adapt to vbscript and your
problem:

http://msdn.microsoft.com/library/d...ples_vb01_8.asp

HTH,

BZ

zcsessions@.visionair.com (Zack Sessions) wrote in message news:<db13d9fb.0308251131.3bb5360d@.posting.google.com>...
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93E2DFB86DD18Yazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > I have a table that has a Text datatype column that has gotten some
> > garbage
> > characters in it somehow, probably from key entry. I need to remove
> > the garbage, multiple occurances of char(15). The replace function
> > does not work on Text datatype. Any suggestions?
> One way would be to iterate over the table, and for each row get slices
> of 8000 chars to a varchar value on which you run replace(). You would
> then use updatetext to update the row. A bit tricky, because if first
> got chars 1 to 8000, and removed 6 char(15), you should now start on
> char 7994 for the next batch.
> Not particularly funny, I know.

Thanks for your response.

I actually thought of trying to do it this way and started to write
the code, but I got stuck on how to get the 8000 character chunks. The
way I read the READTEXT description, it does not return the value into
a local variable. I know how to get the first 8000 characters into a
local varchar, but I haven't figured out how to get any remaining 8000
character chunks. Care to give me a little more help?

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to code in my application. I want that when an Insert Query is executed, a BEFORE/AFTER TRIGGER should be executed to replace the NULL values coming from Insert QUery to "-".

I also want to know whether to use a Trigger or a Function/Stored Procedure.

I'd simply add the logic to your T-SQL insert statement with the ISNULL function. If its wrapped in a stored procedure it may look like something like the following:-

Code Snippet

CREATE PROC Test

@.Var1 CHAR(1),

@.Var2 CHAR(1)

AS

INSERT INTO YourTable (Var1, Var2)

VALUES ( ISNULL(@.Var1, '-'), ISNULL(@.Var2, '-') )

Using an INSTEAD OF trigger may be possible but that would seem a little over complicated for this.

HTH!

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>sql

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.
RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>
|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegr oups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>

Replace NULL with "-" using Trigger

Can a Trigger be created to automatically replace NULL values with
"-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
code in my application. I want that when an Insert Query is executed,
a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
coming from Insert Query to "-".
I also want to know whether to use a Trigger or a Function/Stored
Procedure.RP
1) You can create table that contains a column with default value ='-'
create table #t (c int, c1 char(1) default '-')
go
insert into #t (c) values (10)
go
select * from #t
2) Use COALESCE funtion to dispaly a result to the client
create table #t1 (c int, c1 char(1))
go
insert into #t1 (c,c1) values (10,null)
go
select c,coalesce(c1,'-') from #t1
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>|||It is most efficient to code this type of data validation on the front end,
since that will distribute the load. If you have a middle tier, that is the
second best place to do it, again so you can distribute the load. If you do
it on the database server, all of the work occurs there and could lead to
scalability limitations in a heavily used app.
In any case, you could use a store proc for your insert and simply use
COALESCE on all fields in the INSERT statement inside that sproc. A trigger
would work too, but would be by far the least scalable of the methods. I
would avoid a function for this need.
TheSQLGuru
President
Indicium Resources, Inc.
"RP" <rpk.general@.gmail.com> wrote in message
news:1187163145.162514.295500@.g12g2000prg.googlegroups.com...
> Can a Trigger be created to automatically replace NULL values with
> "-"? Suppose I have an Entry Form with 20 Text Boxes. I don't want to
> code in my application. I want that when an Insert Query is executed,
> a BEFORE/AFTER TRIGGER should be executed to replace the NULL values
> coming from Insert Query to "-".
> I also want to know whether to use a Trigger or a Function/Stored
> Procedure.
>

Replace Function

Dear All,
In my Item Master table, the item column has values like 'printing' in more
than 1000 rows. This was a data entry error. Now my user want all the
instances of 'printing' to be replace by 'printed'. Is there any ways to
update the entire rows in one go, or I will have to change the name from VB
ror by row, or using cusror in SQL.
Please help.
Thanks & Regards,
PramodYou didn't provide much details, but assuming you have a column named "x" fo
r which some rows
currently have the string "printing" and you want to change that string to "
printed", then it is
dead simple:
UPDATE tblname
SET x = 'printed'
WHERE x = 'printing'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pramod@.luxoroffice.com> wrote in message news:%235smP8lHFHA.3500@.TK2MSFTNGP14.phx.gbl...[c
olor=darkred]
> Dear All,
> In my Item Master table, the item column has values like 'printing' in mor
e
> than 1000 rows. This was a data entry error. Now my user want all the
> instances of 'printing' to be replace by 'printed'. Is there any ways to
> update the entire rows in one go, or I will have to change the name from V
B
> ror by row, or using cusror in SQL.
> Please help.
> Thanks & Regards,
> Pramod
>[/color]|||If when you say "values like 'printing'", you mean that
'printing' is not the entire column value, you may need
something like this.
update ItemMaster set
item = replace(item, 'printing', 'printed')
where item like '%printing%'
Steve Kass
Drew University
pramod@.luxoroffice.com wrote:

>Dear All,
>In my Item Master table, the item column has values like 'printing' in more
>than 1000 rows. This was a data entry error. Now my user want all the
>instances of 'printing' to be replace by 'printed'. Is there any ways to
>update the entire rows in one go, or I will have to change the name from VB
>ror by row, or using cusror in SQL.
>Please help.
>Thanks & Regards,
>Pramod
>
>|||try this:-
update <tablename>
set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
from table
steve
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
> If when you say "values like 'printing'", you mean that
> 'printing' is not the entire column value, you may need
> something like this.
> update ItemMaster set
> item = replace(item, 'printing', 'printed')
> where item like '%printing%'
> Steve Kass
> Drew University
> pramod@.luxoroffice.com wrote:
>|||Dear Steve,
My Problem is sloved by the query u provided:
UPDATE ItemMaster
SET ItemName = REPLACE(ItemName, 'Printing', 'Printed')
FROM ItemMaster
WHERE ItemName LIKE '%Printing%'
Thanks,
"Steven Richardson" <steve@.cheltenhamdirectory.com> wrote in message
news:Ocz9w2mHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> try this:-
> update <tablename>
> set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
> from table
>
> steve
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
>