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)).
I'm in a bit of a jam here and will appreciate any help.
I need the SQL code to replace a record if the record is empty.
For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)
I have a large table, tblMessage, which stores e-mail messages in textfields. I need to remove the carriage returns the data in these fields,but I have not yet figured out how to do so.I thought that the way to do this would be with the REPLACE function;unfortunately, of course, the REPLACE function cannot work with TEXTfields. I tried CASTing the text field to VARCHAR(8000); however, someof the rows have more than 8000 characters in the text field, so it bombs.Here is the SQL that I tried:selectmsgID,msgSent,msgFromType,msgFromID,msgSubject,REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') ASnewMessage,msgOriginal,attIDinto tblMessageNewfrom tblMessageI'm at my wit's end. Truncating the text field to 8000 character is anacceptable option, but I can't even seem to be able to do that.I'm using SQL Server version 7.
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.
I need to replace a portion of a url in a column as a result ofchanging servers. Is there a SELECT/REPLACE/UPDATE combination querythat can do this. The table has close to a thousand entries and wouldbe nice if a query can be set to do this. Tried the REPLACE examplein the BOOKS ONLINE but it creates syntax error, apparently because itdoes not like the characters in the url and/or wildcards. I don't needto replace the entire url, only the portion before ".com". Thanks inanticipation of your help.Pradip Sagdeo
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
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
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?
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.
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.
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
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.
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?
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.
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
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?
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.
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
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 ??
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?
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.
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...
Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T.
Concerns: It has to only replace the first letter, if there are other "u"s in the sku, they need to remain. Not all skus start with u, those that don't should remain the same.
I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical.
I have this so far but I know its not correct: set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+ 'T')
I'm the assistant to the assistants assistant and have no SQL base at all but have to change dates in our SQL database. I've got the script for how to do an update/replace for non date fields and have been successful at that. Unfortunately when I try to use the script to change date fields it does not work. It goes through the process and acts like it is working (even asks me if I want to update the records) but when I look in the database the dates have not changed. Below is the script that I am using, any help you can give me will help me keep my job (and pay for the beer I'm sure to need after work today) Thanks in advance for your help. I am trying to change the date '30/08/2007' to '28/08/2008'.
My Script:
update Main_table set valuedate=replace(valuedate,'30/08/2007','28/08/2008') where country='FR' and valuedate='30/08/2007'
Dear All, is there anyway to replace right outer join? coz,i'm trying to create an index on indexed view. i'm getting error, that too because of outer join. how can i replace this?
Vinod Even you learn 1%, Learn it with 100% confidence.
I have a string with multiple carriage returns in it. I need to removethe second carriage return but leave all the rest. Anyone know of afunction that will do this?I've come up with this:/***********************************************/DECLARE @MyString varchar(100)SET @MyString='Line one.Line two.Line three.Line four.'SELECT @MyStringSELECT @MyString = LEFT(@MyString, CHARINDEX(CHAR(13), @MyString,CHARINDEX(CHAR(13), @MyString, 0)+1)-1)+ RIGHT(@MyString, LEN(@MyString)-(CHARINDEX(CHAR(13), @MyString,CHARINDEX(CHAR(13), @MyString, 0)+1)+1))SELECT @MyString/***********************************************/However, if there is a less convoluted way of doing this, I'd love toknow.
