Replacing NULL value in multiple columns in a table

Hi,

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

Thanks in Advance
BobBF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
>
Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL
>
But because there are lots of columns this is pretty much work, also
there are multiple tables.
>
Is there an easy way to replace all NULL values in all columns in a
table?

First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.

No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:

SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great,

Thanks a lot, with the query I can create the new script much and much
easier.

I replaced the o.name with the tables which I new they had the problem
and now I have have all columns to replace the values.

Thanks a lot.

Regards, Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

>
First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.
>
No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:
>
SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..
I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi
|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:

> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
sql

Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column
)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:

> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>

Replacing CRLF's with <BR> in a view

Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>

Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.

replace"***" in image path field.

I am hoping someone will be able to help me with this.
i have a table of accountholders with the following fields
account_no
account_name
image_path
the image_path field has a defaultvalue images/***.jpg
I am looking for a query code to update the value in the image_path field by replacing the *** value with the value of the

account_no fieldupdate accountholders
set image_path = replace(image_path, '***', account_no)

EDIT: safer would be:

update accountholders
set image_path = replace(image_path, '***', account_no)
where image_path='images/***.jpg'

Replace zeros and nulls with 1 in table -- Using case, but not working

Hi folks,

I'm doing calculations based on data in a table, but the data has some
zeros in the field I'm dividing by. I'm trying to write a script to
replace any field with 0 or null with 1, but it's not working. HEre's
what I've got:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

My table is data_unsorted and deptcode and type are my primary keys
Volume is the item I want to put 1 if null or zero, and I'd thing the
above statement would work, but it doesn't. This table has 383 rows,
and it says it updates 383 rows, but when I run the following query to
test:

select a.deptcode, a.type, a.volume
from data_unsorted a
where a.AveMonthVolume = 0 or a.AveMonthVOlume is null

It didn't work... still TONS of nulls and zero's. Is there a trick to
this?

Thanks,

Alex.Alex,

Try this:

update YourTable
set Col = 1
where Col = 0 or Col is null

Shervin

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310091122.fc83cd5@.posting.google.co m...
> Hi folks,
> I'm doing calculations based on data in a table, but the data has some
> zeros in the field I'm dividing by. I'm trying to write a script to
> replace any field with 0 or null with 1, but it's not working. HEre's
> what I've got:
> Update A Set A.deptcode = A.deptcode,
> A.type = A.Type,
> A.Volume = (case A.Volume
> When Null Then 1
> When 0 then 1
> Else A.Volume
> End)
> From Data_Unsorted A Join Data_Unsorted B On
> A.deptcode = B.deptcode and A.type = B.Type
> My table is data_unsorted and deptcode and type are my primary keys
> Volume is the item I want to put 1 if null or zero, and I'd thing the
> above statement would work, but it doesn't. This table has 383 rows,
> and it says it updates 383 rows, but when I run the following query to
> test:
> select a.deptcode, a.type, a.volume
> from data_unsorted a
> where a.AveMonthVolume = 0 or a.AveMonthVOlume is null
> It didn't work... still TONS of nulls and zero's. Is there a trick to
> this?
> Thanks,
> Alex.|||Alex (alex@.totallynerd.com) writes:
> Update A Set A.deptcode = A.deptcode,
> A.type = A.Type,
> A.Volume = (case A.Volume
> When Null Then 1
> When 0 then 1
> Else A.Volume
> End)
> From Data_Unsorted A Join Data_Unsorted B On
> A.deptcode = B.deptcode and A.type = B.Type

You compare A.Volume to NULL, but NULL is never equal to NULL or
anything else. Write the CASE expresssion as.

CASE WHEN volume IS NULL THEN 1
WHEN volume = 0 THEN 1
ELSE volume
END

or

CASE coalesce(volume, 0) WHEN 0 THEN 1 ELSE volume END

The coalesce function returns the first non-NULL value in the list.

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

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

Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "a good years work" with the (corrected): "a good year's work". The substring exists within a variety of sentences eg "Paul has produced a good years work", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a column in a sql table - I guess I need some kind of update query method
Thanks
Eddie
Hi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "
a good years work" with the (corrected): "a good year's work". The substrin
g exists within a variety of sentences eg "Paul has produced a good years wo
rk", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a
column in a sql table - I guess I need some kind of update query method
Thanks
EddieHi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

Replace Query

I have 4 records in my table having a field with bit datatype.

I want to change the values of that in one replace statment
without giving where condition.(value 0 by 1 and 1 by 0)

Recd 1) 0
2) 0
3) 1
4) 1

Quote:

Originally Posted by bipinskulkarni

I have 4 records in my table having a field with bit datatype.

I want to change the values of that in one replace statment
without giving where condition.(value 0 by 1 and 1 by 0)

Recd 1) 0
2) 0
3) 1
4) 1

use an SQL update

update dbo.tblYourTableName
set YourFieldName = case YourFieldName when 0 then 1 else 0 end

Jim :)sql

Replace on a text field.

I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?njjones,

Look at Full Text Indexing in Books Online (BOL).

If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

macka.|||I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?|||Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

SELECT COUNT(*)
FROM yourtable
WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

macka.|||The following works - seems a little heavy handed for a replacement of a one line function, but any:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%25.224.8.30%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%25.224.8.30%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 11
'modconnect1.qinetiq.r.mil.uk'
select @.pos1 =
patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||njjones,

Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

Thanks.|||The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%,%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%,%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 1
';'
select @.pos1 =
patindex('%,%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

RY

Replace null value

I have NULL value of data field, and I want to replace with '-' character in the crystal report. I try to write this formula "IIF(IsNull(table.field),"-",(table.field))", but the result still Null(blank). Can any body help me?
Thank'sOne option is to create a formula, and then place the formula in the report where the current field is located.

The formula would be:

if isnull(table.field) then "-"
else table.field|||Hi abstract, thanks for ur reply. Now i can fix the problem|||I don't think you understood what I was trying to explain. You need to create a Formula Field; NOT create a formula in the suppress box.

I have used this before, so I know it works.

abstract|||Never mind my last post, I misread something.

abstract|||I think this will work if it is in formula

IIF(IsNull(table.field),"-",(table.field))

Replace Function? SQL2K/EM

Hi,
I need to change some table and field names - is there a way to update
all the occurances in Views and Stored procedures?Update the source code for the views and stored procedures and then redeploy
them. You can use the rows from sysdepends to determine which views and
stored procs are affected by name change of a table.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to change some table and field names - is there a way to update
> all the occurances in Views and Stored procedures?
>|||Do they have to be updated manually?
Visual Studio has tools to do this automatically, does EM have nothing
similar for its source code ?
Tim Dot NoSpam wrote:
> Update the source code for the views and stored procedures and then redepl
oy
> them. You can use the rows from sysdepends to determine which views and
> stored procs are affected by name change of a table.
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...|||> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
EM/SSMS will not automatically rename objects because there is nothing on
the server side that will track dependencies. However, this one of the many
new features included in the upcoming Visual Studio 2005 Team Edition for
Database Professionals.
See http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110493.525079.29150@.i40g2000cwc.googlegroups.com...
> Do they have to be updated manually?
> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
> Tim Dot NoSpam wrote:
>

replace function in SQL?

Hi,

I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.

ThanksThere is a replace function. Look in your transact SQL reference and it will give you the nitty gritty. Maybe something like
Update mytable set mycolumn = replace(mycolumn, 'NIB', 'New')|||I guess I just should have looked a little harder, right after I posted that message, I found out its just:

UPDATE Products
SET ProductName = REPLACE(ProductName, 'NIB', 'NEW')

For some reason, the SQL book that I have doesn't have anything about REPLACE()

thanks anyways|||I'm using Microsoft SQL Server Management Studio and I tried this statement:

UPDATE alumni.enewsLetter
SET sum_nws = REPLACE(sum_nws, '\r\n\', '');

Error:
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

Then I tried this:
SELECT REPLACE(alumni.enewsLetter.sum_nws, '\r\n\', '');

Error:
Msg 107, Level 16, State 2, Line 1
The column prefix 'alumni.enewsLetter' does not match with a table name or alias name used in the query.

Help is appreciaeted.|||

Doesn't work on text fields.

BTW, here is the link to the function description:http://msdn2.microsoft.com/en-us/library/ms186862(d=ide).aspx

You could try changing the field datatype from text to varchar(max), perform your update which I would recommend you change to:

UPDATE field=REPLACE(field,'\r\n\','') FROM table WHERE field LIKE '%\r\n%'

That will tell SQL Server to only update the fields that actually contain \r\n instead of updating all fields.

Then change the datatype back to text again, just incase you have code that relies on the fact that it is a text field.

|||

Motley:

You could try changing the field datatype from text to varchar(max), perform your update which I would recommend you change to:

UPDATE field=REPLACE(field,'\r\n\','') FROM table WHERE field LIKE '%\r\n%'

That query statement gave me an error so I tried this:
UPDATE alumni.enewsLetter
SET sum_nws = REPLACE(sum_nws, '\r\n\', ' ')
WHERE sum_nws LIKE '%\r\n%';

And it says "3 rows affected by last query" but nothing happens. Allthe \r\n are still there. And yes, I did change the data type tovarchar.|||

Try:

SELECT sum_nws
FROM alumni.enewsLetter
WHERE sum_nws LIKE '%\r\n%'

And see what 3 rows it returns. Then I would see if I could figure out why either the LIKE isn't returning the other rows (if any) that contain \r\n, or if there are only 3 rows, then why the replace isn't working properly. Sorry I can't be of more help.

|||That select statement does return the three rows that contains the \r\nin it. The problem is why is the update not replacing those characters.Any other idea?

Again, thanks so much!|||

Then try:

SELECT sum_nws,REPLACE(sum_nws,'\r\n',''),REPLACE(sum_nwsm'\\r\\n','')
FROM alumni.enewsLetter
WHERE sum_nws LIKE '%\r\n%'

See which replace works, I'm too lazy to look up and see if you need to escape \ with a \ or not.

|||I recieved Line 1: Incorrect syntax enar '\\n\\n'.
I tried having just one back slash but still giving me the incorrect syntax error near '\r\n'

Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>

Replace data in SQL server table column

What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.Originally posted by josephjthomas
What is the correct syntax to replace a field data nvarchar(50)

Current data = 0020-10-02
Change = 2003-10-02

Thank you in advance.

Update table_name
set (column_name='2003-10-02)
where (select * from table_name where column_name='0020-10-02')|||Why do you assume it's 2003?

DECLARE @.x varchar(50), @.y varchar(50)
SELECT @.x = '0020-10-02'

SELECT SUBSTRING(@.x,3,2)+'03'+RIGHT(@.x,6)|||It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?|||USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = SUBSTRING(x,3,2)+'03'+RIGHT(x,6)
WHERE x = '0020-10-02'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Or can just had code it in the set|||Originally posted by josephjthomas
It's definitely 2003. The entry was mis-typed.

I tried the Update query but that didn't work. Even adding the missing hypen.

Brett,

If I try what you posted, how does it process the update?
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')|||That worked great! Thank you!|||Originally posted by Ida Hoe
My bad:

UPDATE table_name
SET column_name = '2003-10-02'
WHERE EXISTS
(SELECT *
FROM table_name
WHERE column_name = '0020-10-02')

Where's the coorelation...

Why not a simple WHERE?

That'll update all rows...I was suprised it ran...

USE Northwind
GO

CREATE TABLE myTable99(x varchar(50))
GO

INSERT INTO myTable99(x)
SELECT '0020-10-02' UNION ALL
SELECT '2004-10-02'
GO

SELECT * FROM myTable99

UPDATE myTable99
SET x = '2003-10-02'
WHERE EXISTS ( SELECT *
FROM myTable99
WHERE x = '0020-10-02')

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Sorry. I meant to say Brett's script ran and not Ida Hoe.

Thank you both. :-)

Replace chars in any field of a table

I need to strip some puntcuation from any field in a given table.
I'd rather like to avoid using the replace () for each field in the
table.
Anyone have a nifty way do this?
Is there a special name that I can use in the replace that means the
entire row?
(other than syntax, something like REPLACE(@.ROW,CHAR(39),'') )
tia
RobYou could try the following. It uses a cursor, which I'm sure is a bad
thing. But it should work. I didn't try it out. The cursor grabs
column names for columns that are of a varchar type. That may not be
what you need. But the Replace function requires a string, so there ya
go. Maybe (most likely) someone will have a better way to do this.
Hope it helps.

Jennifer

Declare @.Tbl nvarchar(100)
Declare @.Qry nvarchar(1000)
Declare @.N nvarchar(100)

Set @.Tbl = 'TableName'

DECLARE col_cursor CURSOR FOR
select name
from syscolumns
where id = object_id(@.Tbl)
and xtype = 167
order by colid

OPEN col_cursor
FETCH NEXT FROM col_cursor into @.N

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.Qry = 'Update ' + @.Tbl + ' Set ' + @.N + ' = REPLACE (' + @.N +
', ''-'' , '''')'
EXEC sp_executesql @.Qry
FETCH NEXT FROM col_cursor into @.N
END

CLOSE col_cursor
DEALLOCATE col_cursor

Replace and ntext

I need replace a string in a ntext field.
Any ideas ?
Tks.check following example on pubs database . This will create a new table
pub_info_1 with the replaced data of pub_info table. Following example will
work for TEXT datatype for NTEXT, probably you will have to take care of
datalength which is generally datalength/2 (because unicode takes 2 bytes to
store a character.)
(Take a copy of the actual table before trying anything.)
DECLARE @.orig_str varchar(8000), @.rep_str varchar(8000)
SET NOCOUNT ON
DECLARE @.y int,@.str varchar(8000),@.dtlen int, @.pub_id int
DECLARE @.ptrval binary(16),@.ptrval1 binary(16)
SELECT @.orig_str='new moon books', --old string
@.rep_str='old moon books' --new string
--rep_str should not be greater than 100
SELECT @.pub_id = 0
IF object_id('pub_info_1') is not null
DROP table pub_info_1
CREATE table pub_info_1 (new_txt text)
if object_id('tempdb..#txt_1') is not null
drop table #txt_1
CREATE table #txt_1 (xx text)
WHILE @.pub_id is not null
BEGIN
TRUNCATE table #txt_1
INSERT into #txt_1 values ('')
SELECT @.ptrval1 = TEXTPTR(xx)
FROM #txt_1
SELECT @.pub_id=min(pub_id)
FROM pub_info where pub_id > @.pub_id
-- FROM pub_info where pub_id = '9999'
SELECT @.ptrval = TEXTPTR(pr_info) , @.y=1, @.dtlen = datalength(pr_info)
FROM pub_info where pub_id = @.pub_id
WHILE 1=1
BEGIN
SELECT @.str= replace (substring(pr_info, @.y, 7900), @.orig_str, @.rep_str)
FROM pub_info where pub_id = @.pub_id
UPDATETEXT #txt_1.xx @.ptrval1 null 0 @.str
SELECT @.y = @.y + 7900
IF @.y > @.dtlen
BEGIN
BREAK
END
END
If @.pub_id is not null
INSERT into dbo.pub_info_1
SELECT * FROM #txt_1
END
DROP TABLE #txt_1
--
-Vishal
SJPiola <sjpiola@.msn.com> wrote in message
news:029401c34fc0$8f102160$a401280a@.phx.gbl...
> I need replace a string in a ntext field.
> Any ideas ?
> Tks.

Replace a string in an NTEXT field in sql server

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

NTEXT is depricated in SQL 2005 and was replaced by NVARCHAR(MAX). You can use the REPLACE in SQL 2005

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

|||Can access a sql2000 table with NTEXT field as it is and replace a string in that field using SSIS2005? Please advise. Thanks.|||

As I know in SQL2000 the REPLACE will not accept NTEXT data as parameter, which brings trouble when update TEXT data. If there are less than 4000 chars in the NTEXT column, I'd suggest casting the NTEXT data to NVARCHAR(4000) data so that you can use the REPLACE function. Otherwise the replacing is really ugly. Here is a sample to update TEXT data by replacing string:

USE TempDB;
GO

SET NOCOUNT ON;

CREATE TABLE dbo.data
(
DataID INT PRIMARY KEY,
txt NTEXT -- change to TEXT
);
GO

INSERT dbo.data
SELECT 1, N'bar foodfood food har sammy'
UNION ALL SELECT 2, N'bar sammy food'
UNION ALL SELECT 3, N'bar fooblat sammy'
UNION ALL SELECT 4, N'food';

DECLARE
@.TextPointer BINARY(16),
@.TextIndex INT,
@.oldString NVARCHAR(32), -- change to VARCHAR
@.newString NVARCHAR(32), -- change to VARCHAR
@.lenOldString INT,
@.currentDataID INT;

SET @.oldString = N'food'; -- remove N
SET @.newString = N'fudge'; -- remove N

IF CHARINDEX(@.oldString, @.newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN
SELECT 'Before replacement:';

SELECT DataID, txt FROM data;

SET @.lenOldString = DATALENGTH(@.oldString)/2; -- remove /2

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
DataID
FROM
dbo.data
WHERE
PATINDEX('%'+@.oldString+'%', txt) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @.currentDataID;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN

SELECT
@.TextPointer = TEXTPTR(txt),
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID;

WHILE
(
SELECT
PATINDEX('%'+@.oldString+'%', txt)
FROM
dbo.data
WHERE
DataID = @.currentDataID
) > 0
BEGIN
SELECT
@.TextIndex = PATINDEX('%'+@.oldString+'%', txt)-1
FROM
dbo.data
WHERE
DataID = @.currentDataID;

UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
END

FETCH NEXT FROM irows INTO @.currentDataID;
END

CLOSE irows;

DEALLOCATE irows;

SELECT 'After replacement:';

SELECT DataID, txt FROM data;
END

DROP TABLE dbo.data;

|||

Does your solution apply only if there are less than 4000 chars in the NTEXT column? Here's my next questions: how can I find the max length of those ntext fields? But if the max length exceeds 4000, can I use nvarchar(max) in the sql2005 temp table and, after replacing my string, convert the result back to ntext in the sql 2000 source table? Can I use sql2005 for temp work and keep the final data in sql2000. Thanks.

|||You can use datalength(yourColumn) to find your ntext length.|||lori_Jay's solution looks good but has a serious flaw: the new string replacement is truncated to the old string's length after it goes into the NTEXT field. Please advise. Thanks.|||

Problem can be solved with repalcing:
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
with
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString -- deletes old string
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex 0 @.newString -- inserts new string

replace a column with the contents of another with SQL?

Is there any way to replace the contents of a column with the contents of another column with SQL?

For instance, suppose I want to decrease the field size of the primary key from 15 to 10. In order to do that with Oracle I would need to copy the column data to a temporary location, null out the original column, decrease the size, then copy the keys back.

I am having trouble with the last step. I can't seem to figure out how to copy the keys back into there realitive locations.

Can anyone offer any help?

Thanks,
JaminWhat did you use to copy the data to the temporary location?

If you want to copy data from one column to another:

update table1
set column1 = column2;

(Make sure the data in column2 is clean and will not violate any constraints in column1)|||Will that work if I want to copy the column from a different table?|||Urquel's solution would work if you altered the table by adding another column, copy your primary key column into the new one, decrease size of the primary key column and update your table by copying new column value into the primary key column.

If you don't want to alter the table that way, would you consider this: create table as select * From the original table; truncate the original one, decrease primary key column, insert into original table select * from new table.
But, you might encounter problems if the primary key values are referenced by foreign keys (temporarily remove bindings and enable them after import is done).

Another way: export the table, truncate it, decrease size of the primary key column, import data (using IGNORE=Y). Same thing with referential integritiy as above, of course.