Update A String In A Colunm
Apr 25, 2007
Afternoon all.
I have a table which has a column with has been inserting incorrect data, here is an example. Column name is description and table name is tblClientUsage:
[companyname]: 288bsl | | 13/03/2007
[companyname]: 288asl | | 08/03/2007
[companyname]: RES04sl | | 19/02/2004
[companyname]: RES01sl | | 19/02/2004
[companyname]: AAsl | | 31/12/2005
[companyname]: 652Asl | | 05/02/2007
[companyname]: GAZ1sl | | 20/03/2007
[companyname]: AAsl | | 31/12/1999
[companyname]: 363asl | | 20/01/2007
[companyname]: 288b1sl | | 02/11/2006
[companyname]: 288bsl | | 02/11/2006
Now what has been happening is that is has not been putting the company name in the column, just the text companyname. Now i have a table which has the company name and company id, i can easily join the two tables together but how can i replace just the companyname text in the description column with leaving the text after.
Any help would be great
View 3 Replies
ADVERTISEMENT
Mar 15, 2007
Afternoon all,
I have a colunm sName(nVarChar(255) which has first name and second name in it and is divended with a ",". Can somebody show me to put these in there own colunm sFirstName and sSecondName.
Very Kind regards.
View 1 Replies
View Related
Mar 18, 2008
Hello
Can some body help me in performing this?
I want to move the data from Colunm Value to Colunm CPublicID whenever CCField = 'PublicID' as shown below in green color( Without using Cursor or While loops)
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
101 XYZ PublicID Dimension Flag 103
101 XYZ PublicID Dimension Key 103
101 XYZ PublicID Dimension Flag 230
101 XYZ PublicID Dimension Key 230
101 XYZ PublicID Dimension Flag 5691
101 XYZ PublicID Dimension Key 5691
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
103 XYZ PublicID Dimension Flag 103
103 XYZ PublicID Dimension Key 103
230 XYZ PublicID Dimension Flag 230
230 XYZ PublicID Dimension Key 230
5691 XYZ PublicID Dimension Flag 5691
5691 XYZ PublicID Dimension Key 5691
Also , I need to know the best practice to perform the below process. Which of them is advisable?
I have a table, say as above, which has lots of data, say 100,000 records. I need to look at each row and perform certain updates/ trasnformation to a colunm (Value). I aslo, need to hold the old colunm. I came up with two method
Process A) Clone the table. So I have table2 which is a copy of table1. Read the Table1, update / trasnfored the Table2
Table1 Colunm(Value) will have Old values and table 2 colunm(Value) will have transformed values
Process B) Clone the Colunm. So I have a colunm Value and its clone Value2. Read the table and value for Colunm Value and Update the Value2.
I am using process A. because I beleive , process B, reading and updating same table make sthe entire process slow.
Please advice on th ebest industrial practice of doing this.
Thanks in advance for all the help
View 5 Replies
View Related
Nov 13, 2007
Hello All
I want to creat a colunm from two colunms in a specific format
COL1 COL2 COL3
123456 01-10-2007 12070001 ( 12: From First two digit of Col1, 07 from the year of colunm2 and 0001: if combination 1207 is first else increment)
125647 01-11-2005 12050001
126756 12-25-2007 12070002 (* here since combination of 1207 is repeating it gets increment to 0002)
Can somebody help me please
View 8 Replies
View Related
Jun 15, 2006
hi
i wanted to crete an extra colunm when i retrive a set of rows...
the date colunm must show the current date eg 15/06/2006
currently im writing it as follows
select cost, '15/06/2006' as CDate
from prices
as you can see this is static date i need the date to be current whenever the query is done.
View 4 Replies
View Related
May 22, 2006
When I submit a sql :
select * from view from column=''
the table is a view point to oracle table with ole db provider for oracle
In mssql2000, the result return rows which containing a space ' '
However, in mssql2005, the result return no rows .
Any advise on it?
View 6 Replies
View Related
May 13, 2008
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
View 23 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Dec 23, 2014
I have a column in SQL showing Patient Sex (Female/Male)
How Can I update the Column so that I can see only the first character of the string Female and Male( F and M)?
View 2 Replies
View Related
Jan 15, 2008
Hi experts,
I need to do an update on a field which will update 3 characters within a string. Right now I have '000111000333'. I want to replace '000' starting from the 7th character space with '222'. The first set of zeroes will remain unchange. I want the final outcome to be '000111222333'. How can I do this?
I have tried searching this forum and could not find anything. the closest thing I can find is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81890#360936.
Thanks for the help!
View 2 Replies
View Related
Jan 18, 2008
I have at table that I want to update using another table which contains other values than those in the text column.
table1
id: text:
1 1111, 2222, 3333
2 1234, 2222
3 0123
table2
id: str:
1111 aaaa
2222 bbbb
3333 cccc
1234 qqqq
0123 oooo
I want to replace the text in table1 with the new values so that it looks like below:
table1
id: text:
1 aaaa, bbbb, cccc
2 qqqq, bbbb
3 oooo
My first idea was to split the text column in table1 and insert every new text with its id in a temporary table and then concaternate a new string and update the column.
But how do I manage this?
View 4 Replies
View Related
Mar 21, 2000
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
Thanks in advance, I really appreciate it guys.
View 3 Replies
View Related
Jan 8, 2008
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.
Is there any way to do this so that i do not need to write an update query for every column?
Would appretiate an example if anyone knows how to manage this.
Thanks!
View 2 Replies
View Related
Sep 30, 2006
Hi all,I have a
problem here where I am trying to use SQL Parameters to update a column
in the database, but the problem is that I need to concatenate the same
column to some Text from an Application
This is the Code I Have, But it throws an exception
UPDATE rdm_comments SET commentinfo = commentinfo + 'additional info' where commentid=2356
I get an Error stating that i cannot use the addition operator for a string. Help
View 8 Replies
View Related
Sep 7, 2006
Hi.
I have an update statement that accepts
a user's text input. problem is that the string
terminates the sql update statement if there is
an apostrophe in it. so, headline='i like my brother's car"
willl terminate after "brother". "s car" is read as sql.
how is this resolved?
Code:
Dim MyCmd2 As New Data.SqlClient.SqlCommand("SET ANSI_WARNINGS OFF " & _
"UPDATE EditProfile " & _
"SET headline='"+ Me.tb_headline.Value+"', about_me='"+ Me.ta_aboutme.Value+"', edit_date='"+System.DateTime.Now.ToString+"' WHERE email_address='"+Context.User.Identity.Name.ToString+"' " & _
"SET ANSI_WARNINGS ON ", MyConn)
View 4 Replies
View Related
Jul 3, 2014
Trying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw
USE Config
DECLARE @OldPath varchar(30), @NewPath varchar(30)
-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';
UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO
View 3 Replies
View Related
Jul 14, 2006
I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0))
BEGIN..........
END
END
Ideas? Thanks!
View 1 Replies
View Related
Aug 21, 2015
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
View 3 Replies
View Related
Oct 18, 2006
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies
View Related
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Nov 2, 2006
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
View 6 Replies
View Related
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
View 3 Replies
View Related
Dec 17, 2014
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
View 2 Replies
View Related
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Mar 21, 2007
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
View 7 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
Jul 28, 2015
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
View 8 Replies
View Related
Mar 26, 2007
Hi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash
View 1 Replies
View Related
