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


ADVERTISEMENT

Replacing A Portion Of Text String In Column

Jul 20, 2005

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

View 3 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 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 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 Blank Records With Text

Mar 20, 2006

Hi there,

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)

What would be the best way to achieve this?

Thanks

newbie:o

View 6 Replies View Related

Replacing Characters In A Text Field

Jul 20, 2005

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.

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

Estimate On How Long It Might Take To Full-text Index A Table With 21,000 Rows?

Jan 31, 2005

i need to full-text index a table so that i can easily search the text fields of that table.. the table has about 21,000 rows, and i was wondering how long it might take to full-text index it?

thanks

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

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 First Letter Only In Column

May 17, 2006

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')

Can someone help lead me down the right path?

Thanks in advance!!!

View 4 Replies View Related

Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column. 

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far. 

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

Derived Shape - Replacing A Column With Itself.

Apr 27, 2006



Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA



But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset??

Cheers.




View 13 Replies View Related

Executing Sql Code From Text Field Or From Multiple Varchar(8000) Rows In A Table

Jul 20, 2005

Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn

View 7 Replies View Related

Urgent-Replacing A List Of Characters From A Column

Nov 13, 2001

Hi,
this is my second attempt to get an answer to this question.
We want to strip our firstName column and lastname column of any punctuation that might be present.
What's the best of doing that?
Is my only choice to write a nested REPLACE for each character we want replaced (which will end up being very very long) or is there another way.
thanks
Zoey

View 2 Replies View Related

SQL 2012 :: Replacing Column Names With Values

Nov 4, 2015

I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.

Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.

View 3 Replies View Related

Transact SQL :: How To Bulk Insert Rows From Text File Into A Wide Table Which Has 1400 Columns

Feb 3, 2010

we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008. 

We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns.  The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
 
Is there any proper way to load this kind of files into the db table? 

View 6 Replies View Related

How To Count Particular Text (/) From A Column In A Table?

Jun 16, 2008

I m using SQL Server 2000.
I have Tabel named Topic and have a column name lineage.  lineage has data like following:
//////546707//546707//546707/43213/
Now I want to get records who has only one "/" in it's crreponding lineage column.
 Can somone tell me how to do that in SQL Server 2000?
Thanks
Khushbu

View 2 Replies View Related

Column In Table - Text To Time

Sep 19, 2013

I have the following time value column in a table:

next_run_time
230000
73000
70000

The above actually translates to 23:00, 7:00AM,7:30AM, etc. How can I convert this into a meaningful time value?

[CODE]
SELECT NEXT_RUN_TIME FROM TABLE T1
[CODE]

View 2 Replies View Related

Dead Lock In Table Contains Text Column

Feb 16, 2001

Hi Frineds

I have a table with contains Text column. and which is accessed by each page of the site. It is getting frequent Dead Lock Any body can suggest me a good solution pl.

Thanks.

MuraliB

View 1 Replies View Related

DTS Transfer Table With Text Column Slow

Jun 30, 2004

I am using DTS to transfer some tables from one server to another as part of a migration. We want to be down for as little time as possible, but we need the most up-to-date copy of the database tables in question.

I am currently testing the transfer process in our test environment by migrating the data from one database to another on the same SQL instance.

There are 7 tables to transfer and the total size of the database is 450 MB (with around 117 MB used). The two largest tables have around 17,000 records each.

One table (the header) has no text column and it takes just a few seconds to transfer. The other table (the detail) has two columns, one of which is a text column (actually, its not fair to call it the detail table; the relationship is actually one-to-one, but for the sake of this discussion, let's leave it at that).

The header takes seconds to transfer, but the detail takes up to 18 minutes.

Physically, our test server is quite robust; 2 processors, a 3 disk RAID-5 for the data files and a separate RAID 1 partition for the logs. Performance counters don't indicate any real issues: during the transfer, the disk utilization on the data partition occasionally spikes to a high level, but comes right back down until the next spike (the spikes being separated by about 1 minute. No issues with memory, paging or CPU.

I have removed the clustered index on the affected table as well as the PK. No help.

Are text columns just slow? Is there something that I am missing?

Regards,

hmscott

View 4 Replies View Related

Locate Rows In Table Where Column Value Missing (I Think!)

Feb 8, 2007

SQL 2k, DDL below.I have a simple table with the following data:fldYear fldCode1 fldCode22000 ABC1 ABC122000 ABC1 ABC132001 ABC1 ABC122002 ABC1 ABC122002 ABC1 ABC13I need to know, for every distinct combination of fldCode1 andfldCode2, if there are any years missing.For example,SELECT DISTINCT fldCode1, fldCode2 FROM MyTablereturnsABC1 ABC12ABC1 ABC13I need to know that in 2001 there was no entry for ABC1/ABC13Thanks!Edwardif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyTable]GOCREATE TABLE [dbo].[MyTable] ([fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO

View 2 Replies View Related

Updating A Column In A Table That Contains 50 Million Rows

Feb 27, 2008

I'm looking for some performance assistance on updating a column value in a table that contains approximately 50 million rows. I have a permanent table in another database that has the key column and value to be set. My query is listed below, but I'm afraid it will run quite awhile. Any suggestions would be appreciated.

update mytable
set column2 = b.column2
from mytable as a
join mytable1 as b
on a.column1 = b.column1



There is a one to one relationship between the two tables.

View 8 Replies View Related

Can't Insert Rows In Table With Identity Column

Dec 5, 2007

Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error?

Can anyone help???

Thanks!

View 3 Replies View Related

SQL Server 2008 :: Table And Column Name Where Given Text Is Matching

Jul 31, 2015

I need a script which will tell me Table name and column name where a given text is matching.

Example: search string = "I want to search something"

Output: Table T1 , Column C3.

DB: Microsoft SQL Server 2005 / 2008

View 1 Replies View Related

Text File To SQL Table With Identity Column Failing

Apr 22, 2008

Hi,
I am using data flow task to load data with the source as text file to sql server table with identity column.
After mapping all the columns except identity column, when I execute, the package failing saying
it can not insert null value into identity column. I donno how to get around with it ????
Thnaks,
V

View 6 Replies View Related

Make A Bunch Of Rows From A Table Resemble A Column

Nov 23, 2005

Currently I'm using a UDF and a physical temp table to accomplish thisand I want to know if there's any way of doing it in a query orsubquery instead...In TableA there are a bunch of rows:InvitationID (PK) PartyID Partygoer1 1 Jim2 20 Bob3 2 Frank4 1 Robert5 1 PeteIn TableB are rows:PartyID (PK) PartyDate PartyName1 1/1/2000 WildParty2 1/1/2000 BoringPartyWhen a user runs a query to search for all parties on 1/1/2000 I wantthe result to look like:PartyID PartyDate PartyName Partygoer1 1/1/2000 WildParty Jim, Robert, Pete2 1/1/2000 BoringParty FrankI'm hoping there's a solution to this.Thanks,lq

View 9 Replies View Related

Add Column To Existing Table With Large Number Of Rows

Dec 24, 2007

Hey Guys

i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all

whenever i do : alter table mytable add Updated_date datetime

it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit

can u plz advice on how to add this column. its urgent!!

thanks in advance

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

Store Procedure Saving HTML Text In Table Column

May 22, 2008

Hi All,I am looking for a store procedure or any alternate method which save my html file(s) text (with or without tags) in my table column automatically when I upload my html file to my file system (local hard drive).any help will be appreciate.Thanks in advance. 

View 7 Replies View Related







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