473,549 Members | 2,616 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to run replace on all columns

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

Feb 22 '07 #1
6 15556
JackpipE (pi*******@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 22 '07 #2
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.

Feb 23 '07 #3
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" <pi*******@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.
Feb 23 '07 #4
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.
Feb 23 '07 #5
On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pi*******@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
Feb 23 '07 #6
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?
Feb 25 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2629
by: Ryan | last post by:
I'm looking for a stored procedure (or query) to search an entire database for a specific string value and replace it with another. I'm sure I saw an SP for this a while back by someone, but cannot find it again. The SP took the search string and replace string as parameters and did the rest. Any ideas where I can find this ? Bear in mind,...
4
5151
by: Stuart E. Wugalter | last post by:
Here is a sample of what I want to do: Table 1 ID FIELD1 FIELD2 1 A T 2 G C 3 T C Table2 ID FIELD1 FIELD2
0
1500
by: Michael Seele | last post by:
hi! i need a list of all primary keys in my database! for this reson i build these sql-statement: /SELECT pg_class.relname AS tableName, pg_constraint.conname AS pkName, pg_constraint.conkey AS columns FROM pg_catalog.pg_constraint INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid
5
6696
by: enno | last post by:
Dear Community, We have a problem with null-Bytes in varchar-Columns, which are not handled correctly in our application. Therefor we try to filter them out using the Transact-SQL REPLACE function. The Problem was, that the REPLACE-function didn't behave the way we expected. Following Example demonstrates the behavior:
4
1594
by: simon | last post by:
hello, i am displaying a dataset in a datagrid, for one of the values being displayed it either comes back as a 1 or a 0, which is currently bound to a column in the datagrid what i'd like to do is that if the value equals 1, replace that value with a small icon image. if it equals 0 then have nothing display in that column. can this be...
2
8922
by: Curtiosity | last post by:
I have done a create or replace view called creditcard1. If I do a "select * from creditcard1" it retrieves the data just fine. If I try to do a statement where I am listing the column names it doesn't recognize them. create or replace view creditcard1 as select pidm ,tbraccd_term_code as "Term" ,tbraccd_detail_code as "Detail_Code"...
52
6273
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the...
8
17495
by: Joe Cool | last post by:
I need to map several columns of data from one database to another where the data contains multiple spaces (once occurance of a variable number or spaces) that I need to replace with a single space. What would be the most efficient way to do this? I am using SQL2K. I was thinking a function since I know of no single Transact-SQL command that...
0
1185
by: kmonroe6 | last post by:
I'm looking for a way to do a search and replace on *all* columns in a table (SQL Server 2005). I have a table with over 500 columns; some columns are text (nvarchar) some are INT or BIT. I have not used cursors but I should be able to pick it up... In search the Web, I found various partial solutions and I think something like the below...
0
7451
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7720
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7959
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6044
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5369
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3483
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1061
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.