Replacing All The ' In A Table

Nov 24, 2006

Hi all

I need to remove all the ' in a table and i am having some problems with the script, any help would be great

Thanks

Richard

View 1 Replies


ADVERTISEMENT

Replacing Illegal Characters In Table

Dec 4, 2006

i'm trying to figure out a way to replace a handful of "Illegal" characters in our SQL tables. We are using Project Server and some of these characters are causing errors or other issues in OLAP Cube.

I'm trying to figure out a way to change the following characters to an underscore ( _ ) :

illegal characters are: / ( ) . , ' : - &

Can I just create a SQL query that loops through a column to replace all of these characters at once? or do I have to replace one character at a time?! I tested replacing one at a time and it works with the REPLACE function.

I'm not all that familiar with MSSQL, I've spent past few years working with MySQL instead.

how can my query loop? or would using CASE help me out?

thanks. any feedback is much appreciated. If you need to know more, let me know.

regards

View 2 Replies View Related

Replacing Existing Records In A Table

Feb 5, 2007

I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?

View 3 Replies View Related

Updating /replacing Contents Of The Table

Jun 11, 2007

Is it possible to do an update * where in we update all the contents of the table after selecting specific rows of the same table.

for ex:

create table #Result(rowid int identity(1,1),cust_no int,companyname varchar(100),address varchar(200),zip varchar(9));

insert into #Result(cust_no,companyname,address,zip)
where (customertype='r' and customer.comp_id ='11')
group by customer.cust_no, customer.company


select * from #Result where customerstatus='s';

after this i want to update/replace the #Result with whatever results I have from the select statment.

Thanks for your replies.

View 5 Replies View Related

Replacing Text In A Column For All Rows In The Table?

Oct 24, 2012

I have a column like this:

Table: User
name, field1
Jack 1000|1001|1003
Berg 2000|1001|2004
Paul 1001|1000
Jane 1001

Now, I would like to replace all "1001" with nothing, and also remove the "|"-separator behind 1001 if it exists, basically removing both "1001" and "1001|", so the resulting table looks like this:

name, field1
Jack 1000|1003
Berg 2000|2004
Paul 1000
Jane

My tries, been plenty, but here are some:

Code:
UPDATE UserTable
SET field1 = REPLACE(field1, '1001', '')
UPDATE UserTable
SET field1 = REPLACE(field1, '1001|', '')

But the above queries replaces field1 only if the whole field matches '1001' or '1001|'...

The above queries do work, just like I want them to. I just happened to write them in this order on this post... did not do a copy of the actual query.

View 1 Replies View Related

Replacing NULL Value In Multiple Columns In A Table

Dec 18, 2006

Hi,I have some tables where I import data in, lots of field have gotten aNULL 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 NULLBut because there are lots of columns this is pretty much work, alsothere are multiple tables.Is there an easy way to replace all NULL values in all columns in atable?Thanks in AdvanceBob

View 2 Replies View Related

SQL Server 2012 :: Replacing String (Particular Part Of A Row In A Table With New Value)

Oct 7, 2015

I'm trying to replace a particular part of a row in a table with a new value.

The row is called "DataPath" and it has a lot of values like so:

mashOperationsComponent Data FilesSantec

I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.

I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..

UPDATE dbo.Part
SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash')
WHERE DataPath like 'mash\%'

So for example, it would change the mash above to

company.localsharesDepartmentsOperationsComponent Data FilesSantec

View 2 Replies View Related

Replacing Tables In SQL Express By An Updated Table Of The Same Name Through VB2008 Code

Apr 17, 2008

Hello,

I have an application written in Visual Basic 2008, using a SQL Express 2008 database. This application runs in several locations on stand-alone PC's and there is no network connecting them.
I have to maintain a SQL database in a central location an would like to be able to distribute updated tables from that central location by using an USB stick and replace the tables of the same name in the different locations with the updated ones on my USB stick.
This I would like to do using code within my VB2008 application.

Can someone please help me on the way to achieve this. Is it possible to give some concrete code examples, as I am a newbie as a SQL Server user.

Many thanks and greetings,

Michel


View 5 Replies View Related

T-SQL (SS2K8) :: Pivot Table - How To Get Right Count In First Data Mining Replacing Zeros

Mar 24, 2014

I run this code:

SELECT
Gruppo_Assegnatario,
[100] as stato1, [101] as stato2, [102] as stato3
FROM
(
select

[Code] ...

That extracts only zeros (columns "stato1", "stato2", "stato3"):

Gruppo_Assegnatariostato1stato2stato3
SDB_BE Vita Antiriciclaggio0 00
SDB_BE Vita Assistenza clienti000
SDB_BE Vita Emissione000
SDB_BE Vita Gestione Rendite000
SDB_BE Vita Liquidazioni000

[Code] ....

Unlike the "SourceTable":

select
CASE_ID_,
Stato,
Gruppo_Assegnatario
FROM TicketInevasiPerGruppoEStato
extracts

CASE_ID_ Stato Gruppo_Assegnatario
HD0000003736734 AssegnatoSDB_GBS Variazione
HD0000003736739 AssegnatoSDB_GBS Variazione
HD0000003736743 AssegnatoSDB_GBS Variazione
HD0000003736783 AssegnatoSDB_GBS Variazione
HD0000003736806 SospesoSDB_BE Vita Selezione

[Code] ....

How can I get the right count in the first data mining replacing the zeros (columns "stato1", "stato2", "stato3")?

View 5 Replies View Related

SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic

Oct 20, 2014

I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):

UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'

[Code] ....

The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.

The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.

What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.

View 9 Replies View Related

Replacing FIRST In VIEWS

Aug 28, 2000

Hi, I'm trying to conver an Access database to SQL 7, but I can't find a easy way to replicate the aggregate function FIRST in SQL, can anyone give me some advise on how to "fake" the FIRST agg function in SQL7? Thanks

View 1 Replies View Related

Replacing Characters In SQL

Aug 13, 2004

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?

Thanks

View 2 Replies View Related

Replacing Value In Column?

Nov 20, 2013

I want to replace the value in a column with the content from listbox.

I have wrote the code like,

Dim i as Integer
dim sql as string
For i = 0 to Customerslist1.ListCount-1
sql = "UPDATE master (gstl) VALUES(" +chrb(34)+Customerslist1.Cell(i,8)+chrb(34)+")"
app.Gudangstock.SQLExecute(sql)
app.Gudangstock.commit
next
msgbox"insert ok"
UpdateCustomerList1

but no error found and the sql command is not executing.

View 5 Replies View Related

Replacing Blanks

Aug 31, 2005

Nevil Mascarenhas writes "Hi SQL team,

I am just a beginer in SQL

Here is sample output of a SQL query

Alarm No Site Name Startdate Starttime

7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00

In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX

Output required is

Alarm No Site Name Startdate Starttime

7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00

Can you please guide me."

View 1 Replies View Related

Replacing Values

Nov 18, 2005

Hi i have 2 cols

col1 col2
A1 21
A2 22
A3 21
A4 23

I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.

So the report should look like

col1 col2
A1 21
A2 0
A3 21
A4 0

can you please tell me how to do that

Thanks
vic

Vicky

View 10 Replies View Related

Total Replacing

Jul 20, 2005

Hi,Is it possible to do from one script? We have a set of user's tables like"tbl%". We can get this list very easy using this script:SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BYname;We need to change some column names if these names are in a special listthat we have. What can we do? Use FOR EACH ROW? Or what?So, I need to get a column list for each table and check if every columnname is equal to one of the names from the list and then if YES replace itby something or add some symbol to this name. Terrible or possible?Regards,Dmitri

View 4 Replies View Related

Replacing A Character

Jul 30, 2007

Hi

I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given characterstring with another one in this column. Any assistance on this is highly appreciated.

Thanks!
Santhosh

View 1 Replies View Related

Replacing Views In SQL CE

Jan 15, 2008

Hi all,

We are trying to migrate a SQL Server 2005 database/schema onto SQL CE. The original SQL Server 2005 schema uses views. And VIEWs are not supported on SQL CE. What can we use as an alternative?

Here are the option I can think of:


Use Tables instead of views : We are currently using this approach. But the disadvantage is that data remains on the DB. We need to clean and reuse the table everytimeCan we use a DataSet or DataTable instead? Any sample code available?
Can we replace them by some in-memory data structure?

View 3 Replies View Related

Replacing Text

Jan 21, 2008

Hi,

I would like to replace all instances of a code that starts with 'GEM' with the word 'Perfect'', and all instances of the code that starts with 'GOLD' replaced with 'Imperfect'.

For example, if I have a table named STONES, and a field within the table STONES.Code contained codes such as 'GEMPART4000', 'GEMPART5000', 'GOLDSIDE2300' and 'GOLDSIDE3000', I want to return the first two codes as the words 'Perfect' for each and the last two codes with the word 'Imperfect' for each.

Similarly, how would it be written so I could select a middle part of the code to use as the trigger to replace the code with something different, e.g. if I wanted to use the 'PART' from the first two codes?

I have discovered that the REPLACE and CASE functions don't work with % (so I am looking for any code that starts with what I stipulate but can end with anything (or using it on either side of the middle part of the code)).

Any help would really be appreciated please.

View 5 Replies View Related

Replacing Column Value

Dec 3, 2007

Hi Guys,

I am faced with a problem that is giving me headaches. I have t1, t2 and t3. I t1 I have students that have a reference to t2 which are the schools they belong to. The problem arises when I see that there is redundancy in t2. There more records for the same schools. This was posible in giving the same school (postcode and name make it the same school) a different ID. In t3 I removed these redundant schools by using fuzzy grouping.

My problem is I want to ensure that the students are put using the schools from t3 instead of t2. So what I need to do is is to replace the redundant id from t2 with the correct id from t3.

Please help. Thans in advance

View 3 Replies View Related

Help Replacing 'where Not Exists' In A Query

Jul 20, 2007

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table.  As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table.  Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan

View 7 Replies View Related

Replacing Quotes With Stringbuilder

Aug 2, 2007

Hi, below is the query that i use.update user set user_description=' "+ userDesc +" ' where user_id = 1;userDesc is the value taken from a textbox and is supposed to be used with the update query.userDesc = "kad'nsad'kasnd'nak";The quotes in userDesc is affecting the update. Is there anyway to replace quotes with ' using stringbuilder?

View 1 Replies View Related

Sql Query Replacing Values

Apr 1, 2004

Hi, I have the following query.

sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"

Status is either 0,1,2 or 3

How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.

So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false

So when the reader reads Expr4 its either true or false.

Is this possible i a query?

View 1 Replies View Related

Replacing A Column With A Foreign Key

Dec 24, 2005

In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table
?

View 2 Replies View Related

Help Please!!! Replacing Returns And Tabs

Jan 10, 2002

Hi,
how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.
thanks
Zoey

View 1 Replies View Related

Triggers Replacing FK And Constraints

Mar 1, 2001

I am thrawn at the deep end

I have been given the task of moving certain tables (that have PK, FK Constraints) from an existing Database (A) to form another Database (B) but making sure all the references to other tables in the original DB (A) are not affected. I was talled that triggers can solve this but I am not sure where to start. The Question is what I am looking for to replace, and how ??

Please help !!!!!!
Saad

View 3 Replies View Related

Help - Replacing Data In Tables

Sep 4, 2005

Need to update data from one table with data from another table.

I'm very, very new to MS SQL Server

I have the following two tables.
First table
tblEmp
Field - empno

Second table
tbltemp
Field - oldempno
Field - newempno

tblemp.empno = tbltemp.oldemno

I need to replace the data in tblEmp.empno with tbltemp.newempno where tblemp.empno = tbltemp.oldempo.

Thank you.

View 1 Replies View Related

Replacing Data From SELECT/WHERE

Jun 26, 2001

Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.

Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?

For example,

SELECT * FROM table1
WHERE column1 = 'text'

and replace 'text' with say, 'newtext'

Any help would be appreciated!

Regards,

Chandran

View 5 Replies View Related

Replacing One Part Of A Number

Dec 1, 2004

I need to change the first digit (0) in a number, like 05678494 and replace it with 0046 so it would look like 00465678494 when it is done.
I have tried to use the REPLACE function without any success.
Can anyone help me?

View 4 Replies View Related

Replacing A Text Globally

Sep 18, 2007

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

and replace all of them with "abc" without affecting the databases an application.

Can u please help me with step by step guidance?

muralidaran r

View 14 Replies View Related

Replacing Some Names In Column But Not All

Dec 2, 2013

I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like

UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';

I need basically to swap the Name value of these without affecting any other values.

View 4 Replies View Related

Replacing A Reserved Keyword

Feb 11, 2014

I've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...

CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT

[code]....

View 3 Replies View Related

Replacing The New Version Of The Database

Aug 23, 2005

Hi friends

I have a database where i need to replace with a new version of the same one.
Can anyone suggest the best possible and reliable way of doing that

regards
Vic

Vicky

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved