How to run replace on all columns

[JackpipE]

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPO KEN)
but this is time consuming and would like to automate this process as
much as possible.

Update PROFILE
SET LANGUAGES_SPOKE N = replace(cast(_L ANGUAGES_SPOKEN as
nvarchar(255)), char(13)+char(1 0),':')

Thanks,
JP

[Erland Sommarskog]

Re: How to run replace on all columns

JackpipE (pipe.jack@gmai l.com) writes:

Here is my replace query and I need to run this on every column in my
table. Right now I manually enter the column name (_LANGUAGES_SPO KEN)
but this is time consuming and would like to automate this process as
much as possible.
>
Update PROFILE
SET LANGUAGES_SPOKE N = replace(cast(_L ANGUAGES_SPOKEN as
nvarchar(255)), char(13)+char(1 0),':')

There is no way to loop through the columns in a table in a simple
fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.

For a thing like this I would do:

SELECT 'UPDATE PROFILE SET ' + name + ' replace(substri ng( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROF ILE')
and type_name(xtype ) like '%char'

and the copy, paste and run result.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[JackpipE]

Re: How to run replace on all columns

There is no way to loop through the columns in a table in a simple

fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.
>
For a thing like this I would do:
>
SELECT 'UPDATE PROFILE SET ' + name + ' replace(substri ng( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROF ILE')
and type_name(xtype ) like '%char'
>
and the copy, paste and run result.

Well that simplify my job but still does not automate the process to a
point where one query execution will take care of entire table.

Thanks.

[Roy Harvey]

Re: How to run replace on all columns

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.

declare @tblname nvarchar(60)
set @tblname = 'PROFILE'

SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)), char(13)+char(1 0),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname
ORDER BY C.id, C.colid

Output from one test exeuction:

UPDATE HoldEventsTable
SET TelephoneCallID = replace(cast(Te lephoneCallID as
nvarchar(255)), char(13)+char(1 0),':'),
Time= replace(cast(Ti me as
nvarchar(255)), char(13)+char(1 0),':'),
Event= replace(cast(Ev ent as
nvarchar(255)), char(13)+char(1 0),':');

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pipe.jack@gmai l.comwrote:

>There is no way to loop through the columns in a table in a simple
>fashion. This is because that it would rarely make any sense; columns
>in a table are supposed to described distinct attribuets.
>>
>For a thing like this I would do:
>>
> SELECT 'UPDATE PROFILE SET ' + name + ' replace(substri ng( ' +
> name + ', 1, 255), char(13) + char(10), '':'')'
> FROM syscolumns
> WHERE id = object_id('PROF ILE')
> and type_name(xtype ) like '%char'
>>
>and the copy, paste and run result.

>
>
>Well that simplify my job but still does not automate the process to a
>point where one query execution will take care of entire table.
>
>Thanks.

[JackpipE]

Re: How to run replace on all columns

On Feb 22, 8:52 pm, Roy Harvey <roy_har...@sne t.netwrote:

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.
>
declare @tblname nvarchar(60)
set @tblname = 'PROFILE'
>
SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)), char(13)+char(1 0),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname
ORDER BY C.id, C.colid
>
Output from one test exeuction:
>
UPDATE HoldEventsTable
SET TelephoneCallID = replace(cast(Te lephoneCallID as
nvarchar(255)), char(13)+char(1 0),':'),
Time= replace(cast(Ti me as
nvarchar(255)), char(13)+char(1 0),':'),
Event= replace(cast(Ev ent as
nvarchar(255)), char(13)+char(1 0),':');
>
Roy Harvey
Beacon Falls, CT
>

Roy,

I had different output when I ran your query:
_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':');

It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.

[Roy Harvey]

Re: How to run replace on all columns

On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pipe.jack@gmai l.comwrote:

Roy,
>
>I had different output when I ran your query:
>_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
>_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
>_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':'),
>_NAME= replace(cast(_N AME as nvarchar(255)), char(13)+char(1 0),':');
>
>It looked like select statement output with 17 rows (17 columns in the
>table) like the above. No UPDATE or SET function.

I assume you changed the column names to all be _NAME, rather than the
query actually returning that.

The missing UPDATE and SET is what I would expect if the query was
written to filter out the first column. The specification said every
column, so I did not write the query to allow for that. In the first
CASE the first WHEN test would have to change from the simple:

WHEN C.colid = 1

To something like:

WHEN C.colid = (SELECT MIN(x.colid) FROM syscolumns as X WHERE X.id =
O.id AND <whatever filtering was used in the outer WHERE clause>)

Likewise the subquery in the last CASE would have to add the same
tests to match the WHERE clause.

Roy Harvey
Beacon Falls, CT

[Roy Harvey]

Re: How to run replace on all columns

Please post responses to the newsgroup, not to email. This promotes
the basic function of newsgroups, sharing information. It also means
that more than one person is reading and thinking about your problem.

If you did not add a WHERE clause test to limit the columns then I am
quite surprised that the UPDATE line did not appear in the output.
That would seem to indicate that there is no colid = 1 for the table.
I was able to create that condition by doing an ALTER TABLE to drop
the first column, but it is a condition my original query did not
allow for.

Did you try the alternate syntax I provided? What do you get from
this query?

SELECT MIN(colid), MAX(colod), count(colid), count(distinct colid)
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname

As for executing the code from inside the stored procedure, it is
possible. The command is spread over many rows, so the first step is
to turn that query into a cursor and step through the rows
concatenating all of them into a single string. Then you would have
to use dynamic SQL to execute it. Before doing that I suggest reading
this article very carefully: http://www.sommarskog.se/dynamic_sql.html

Roy Harvey
Beacon Falls, CT

>Roy,
>
>Thank you for your time and helping me out. In my last reply the
>output I copied was wrong. Here is the code and output I get from your
>script:
>declare @tblname nvarchar(60)
>set @tblname = '_PHYSICIAN_PRO FILE'
>
>SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)), char(13)+char(1 0),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname
ORDER BY C.id, C.colid
>
>======= output 42 rows =========
>
_NAME= replace(cast(_N AME as
>nvarchar(255)) ,char(13)+char( 10),':'),
_SPECIALTY= replace(cast(_S PECIALTY as
>nvarchar(255)) ,char(13)+char( 10),':'),
_GENDER= replace(cast(_G ENDER as
>nvarchar(255)) ,char(13)+char( 10),':'),
_SPECIAL_INTERE STS= replace(cast(_S PECIAL_INTEREST S as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PRACTICE_HIGHL IGHTS= replace(cast(_P RACTICE_HIGHLIG HTS as
>nvarchar(255)) ,char(13)+char( 10),':'),
_TRAINING_POST_ GRADUATE_EDUCAT ION=
>replace(cast(_ TRAINING_POST_G RADUATE_EDUCATI ON as
>nvarchar(255)) ,char(13)+char( 10),':'),
_BOARD_CERTIFIC ATION= replace(cast(_B OARD_CERTIFICAT ION as
>nvarchar(255)) ,char(13)+char( 10),':'),
_LANGUAGES_SPOK EN= replace(cast(_L ANGUAGES_SPOKEN as
>nvarchar(255)) ,char(13)+char( 10),':'),
_INSURANCE_ACCE PTED= replace(cast(_I NSURANCE_ACCEPT ED as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PERSONAL_INFOR MATION= replace(cast(_P ERSONAL_INFORMA TION as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS1_1= replace(cast(_A DDRESS1_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS1_2= replace(cast(_A DDRESS1_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS1_3= replace(cast(_A DDRESS1_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE1= replace(cast(_P HONE1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_FAX1= replace(cast(_F AX1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK1= replace(cast(_I N_NETWORK1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS2_1= replace(cast(_A DDRESS2_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS2_2= replace(cast(_A DDRESS2_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS2_3= replace(cast(_A DDRESS2_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE2= replace(cast(_P HONE2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK2= replace(cast(_I N_NETWORK2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS3_1= replace(cast(_A DDRESS3_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS3_2= replace(cast(_A DDRESS3_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS3_3= replace(cast(_A DDRESS3_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE3= replace(cast(_P HONE3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK3= replace(cast(_I N_NETWORK3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS4_1= replace(cast(_A DDRESS4_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS4_2= replace(cast(_A DDRESS4_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS4_3= replace(cast(_A DDRESS4_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE4= replace(cast(_P HONE4 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK4= replace(cast(_I N_NETWORK4 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS5_1= replace(cast(_A DDRESS5_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS5_2= replace(cast(_A DDRESS5_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS5_3= replace(cast(_A DDRESS5_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE5= replace(cast(_P HONE5 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK5= replace(cast(_I N_NETWORK5 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS6_1= replace(cast(_A DDRESS6_1 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS6_2= replace(cast(_A DDRESS6_2 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_ADDRESS6_3= replace(cast(_A DDRESS6_3 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_PHONE6= replace(cast(_P HONE6 as
>nvarchar(255)) ,char(13)+char( 10),':'),
_IN_NETWORK6= replace(cast(_I N_NETWORK6 as
>nvarchar(255)) ,char(13)+char( 10),':'),
META_SRC_URI= replace(cast(ME TA_SRC_URI as
>nvarchar(255)) ,char(13)+char( 10),':');
>
>I don't think I filter out anything yet I don't have UPDATE or SET
>function and the output from this query is just like a select
>statement that does not execute the replacement.
>
>Is there a way actually execute the replace from this stored procedure?