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_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.
Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')
Thanks,
JP 6 15522
JackpipE (pi*******@gmail.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_SPOKEN)
but this is time consuming and would like to automate this process as
much as possible.
Update PROFILE
SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
nvarchar(255)),char(13)+char(10),':')
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(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'
and the copy, paste and run result.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
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(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
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.
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(10),'':'')' +
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(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pi*******@gmail.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(substring( ' + name + ', 1, 255), char(13) + char(10), '':'')' FROM syscolumns WHERE id = object_id('PROFILE') 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.
On Feb 22, 8:52 pm, Roy Harvey <roy_har...@snet.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(10),'':'')' +
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(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');
Roy Harvey
Beacon Falls, CT
Roy,
I had different output when I ran your query:
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.
On 22 Feb 2007 19:33:42 -0800, "JackpipE" <pi*******@gmail.comwrote:
Roy,
I had different output when I ran your query: _NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'), _NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'), _NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'), _NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');
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
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_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(10),'':'')' +
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(_NAME as nvarchar(255)),char(13)+char(10),':'),
_SPECIALTY= replace(cast(_SPECIALTY as nvarchar(255)),char(13)+char(10),':'),
_GENDER= replace(cast(_GENDER as nvarchar(255)),char(13)+char(10),':'),
_SPECIAL_INTERESTS= replace(cast(_SPECIAL_INTERESTS as nvarchar(255)),char(13)+char(10),':'),
_PRACTICE_HIGHLIGHTS= replace(cast(_PRACTICE_HIGHLIGHTS as nvarchar(255)),char(13)+char(10),':'),
_TRAINING_POST_GRADUATE_EDUCATION= replace(cast(_TRAINING_POST_GRADUATE_EDUCATION as nvarchar(255)),char(13)+char(10),':'),
_BOARD_CERTIFICATION= replace(cast(_BOARD_CERTIFICATION as nvarchar(255)),char(13)+char(10),':'),
_LANGUAGES_SPOKEN= replace(cast(_LANGUAGES_SPOKEN as nvarchar(255)),char(13)+char(10),':'),
_INSURANCE_ACCEPTED= replace(cast(_INSURANCE_ACCEPTED as nvarchar(255)),char(13)+char(10),':'),
_PERSONAL_INFORMATION= replace(cast(_PERSONAL_INFORMATION as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_1= replace(cast(_ADDRESS1_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_2= replace(cast(_ADDRESS1_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS1_3= replace(cast(_ADDRESS1_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE1= replace(cast(_PHONE1 as nvarchar(255)),char(13)+char(10),':'),
_FAX1= replace(cast(_FAX1 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK1= replace(cast(_IN_NETWORK1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_1= replace(cast(_ADDRESS2_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_2= replace(cast(_ADDRESS2_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS2_3= replace(cast(_ADDRESS2_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE2= replace(cast(_PHONE2 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK2= replace(cast(_IN_NETWORK2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_1= replace(cast(_ADDRESS3_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_2= replace(cast(_ADDRESS3_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS3_3= replace(cast(_ADDRESS3_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE3= replace(cast(_PHONE3 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK3= replace(cast(_IN_NETWORK3 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_1= replace(cast(_ADDRESS4_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_2= replace(cast(_ADDRESS4_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS4_3= replace(cast(_ADDRESS4_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE4= replace(cast(_PHONE4 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK4= replace(cast(_IN_NETWORK4 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_1= replace(cast(_ADDRESS5_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_2= replace(cast(_ADDRESS5_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS5_3= replace(cast(_ADDRESS5_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE5= replace(cast(_PHONE5 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK5= replace(cast(_IN_NETWORK5 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_1= replace(cast(_ADDRESS6_1 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_2= replace(cast(_ADDRESS6_2 as nvarchar(255)),char(13)+char(10),':'),
_ADDRESS6_3= replace(cast(_ADDRESS6_3 as nvarchar(255)),char(13)+char(10),':'),
_PHONE6= replace(cast(_PHONE6 as nvarchar(255)),char(13)+char(10),':'),
_IN_NETWORK6= replace(cast(_IN_NETWORK6 as nvarchar(255)),char(13)+char(10),':'),
META_SRC_URI= replace(cast(META_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?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |