Replace Data Using Sql
Aug 4, 2004
I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.
The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00
If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.
I currently have duration as text.
How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)
I need the duration in time format in order to be able to make sum calculations.
I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.
Thank you in advace
George
View 10 Replies
ADVERTISEMENT
Sep 4, 2015
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
View 4 Replies
View Related
Mar 16, 2012
There are 4 tables in a database. such as university, student, professor and worker. university table contains fields named- university_name, student_name, professor_name and worker_name. and student, professor and worker tables contain person_id and person_name.
Now, I want to replace the student_name, professor_name and worker_name data from university table with student table's person_id, professor table's person_id and worker table's person_id.
View 3 Replies
View Related
Aug 18, 2006
I am working with a database containing time series data. In many, cases there is missing data. For example, while there might be a value for 2001-01-01T23:00:00, there is none for 2001-01-01T23:0100 (one minute later). I would like to replace the missing data with data from the previous record (if the previous record is the same date). Is that possible with T-SQL?
View 6 Replies
View Related
Aug 27, 2006
In this project I m using SQL Server 2005 express edtion.I can run select queries but I couldnt run insert and update query.Is there any protection for adding data to the Sql Server express edition ?
thanks.
View 13 Replies
View Related
Apr 29, 2008
I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
View 3 Replies
View Related
May 29, 2006
Hello guys,
I would like to execute a t-sql query and replace a string by another in all records.
I did this :
UPDATE myTable
SET myColumn = replace(myColumn,'old string','new string')
The error I get is :
Argument data type ntext is invalid for argument 1 of replace function.
Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
View 5 Replies
View Related
Aug 29, 2006
Hello,
I have a table in witch i would like to replace all the data form one columm following a criteria.
The criteria is:
Where 1010222222 will be 0020222222
So i want to make a script that enables me to substitute all the data in that columm that begin by "101" for "002"
Any help?
Thanks
View 6 Replies
View Related
Feb 29, 2008
Hi, I was wondering if it is posible as in derived columns to replace an input column with the output one in Data Conversion?
What I can see that when a data conversion is done, only we can do it in a new column and not in the same one we are using as input.
Do you know if this it is posible?}
Thanks for your help!
Beli
View 3 Replies
View Related
Jun 14, 2006
I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text
2 = Report
3 = Background
4 = Index
I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")
Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?
I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.
View 3 Replies
View Related
Apr 14, 2004
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
View 8 Replies
View Related
Nov 12, 2007
I currently have a LoginStatus and LoginName control on my asp.net 2.0 master page. When successfully logged in a message displays "You are logged in as" LoginName. Since I am using the user's e-mail address as the UserName the message will display as "You are logged in as johndoe@foo.com".
What I want to do is have the message display as "You are logged in as " FirstName LastName. FirstName and LastName are two new fields that I added to the table, aspnet_Users.
What I have done is to use the FormView control to display FirstName and LastName; however this always displays the first record in the aspnetdb database.
Is there a cleaner way (than the FormView control) to display the requested data, using the currently logged in user? I have access to Microsoft Expression Web and Visual Studio 2005, but I prefer Expression Web.
Thank you in advance
View 1 Replies
View Related
Feb 15, 2008
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies
View Related
Sep 9, 2015
I am writing an SQL query to find an replace data in a column. I have a table that is filled with
C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata
I would like to replace the c:usersXXXXX part with %userprofile%
The end result would be %userprofile%appdata
I know how to dot the replacement in powershell. it's quite easy
-replace "c:users[^]+","%userprofile%"Â Â
Basically how would transfer this into SQL...
View 10 Replies
View Related
May 8, 2008
Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks
View 7 Replies
View Related
Apr 29, 2008
I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.
View 5 Replies
View Related
Jul 25, 2006
W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
Thank you.
View 5 Replies
View Related
Sep 12, 2014
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies
View Related
Oct 5, 2015
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies
View Related
Jan 10, 2006
Hi -
I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start. I need to update values in a text field with new values from another table. I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue'). But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution. I can make up a simple table that will have all the old values in one column, and the new values in the next. I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.
I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything. I guess I just don't know where to look. Thanks in advance for any help you can give me.
Rebekkah
View 5 Replies
View Related
May 14, 2008
Hi There,
Could someone please tell me why I am getting the above error on this code:
select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit
serviceType (text,null)
Thanks,
Rhonda
View 1 Replies
View Related
Oct 16, 2007
Hi all i have a question regarding sql, i want to replace some characters...
any knows simply how to do this?
I want to replace "999-25000-69" by "9992500069"
grtz
View 1 Replies
View Related
Nov 28, 2006
i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much
View 2 Replies
View Related
Aug 15, 2006
can I use a replace on text type field?
View 1 Replies
View Related
Apr 11, 2006
hi
when i execute ...
select * from Members where address = '6257 Rockwell's'
ERROR :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
Kindly help me how to solve '
View 2 Replies
View Related
Nov 15, 2006
i just wanna replace the last word 'roa' to 'road'
what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.
declare @table table(ad_str1 varchar(20))
insert @table
select 'street road roa' union all
select 'street street'
select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table
update @table
set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road')
where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'
select * from @table
View 9 Replies
View Related
Nov 29, 2007
I have a colunm in a table that has a list of email address.
I want to remove a certain email address from that colunm, but it is in 54 different rows. What is the best way to do this ?
View 2 Replies
View Related
Dec 12, 2007
Dear all,
here is one query,
IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))
i'm trying to tuning myprocedures. i think having is not good enough to use.
how can we replace having in this case.....
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 6 Replies
View Related
May 28, 2008
Hi everyone,
I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because 'IN' must return only one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ac.INDIVIDUAL_ID = 5249
Except
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249); This code gets me close using 'IN'.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in
(SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in
(SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49)
View 4 Replies
View Related
Sep 13, 2006
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
View 1 Replies
View Related
Feb 18, 2007
Hi all,
I made a migration operation and when I am looking inside database I can see the <BR> tags.
The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code.
Thanks in advance.
View 2 Replies
View Related
Aug 22, 2007
I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is @FieldName might be: ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output? Thanks.
View 4 Replies
View Related
Sep 23, 2007
Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this
Project/NEWUSERID/ProjectImages/702d_2.jpg
How can I accomplish this in SQL???
thanks in Advance
Dollarjunkie
View 2 Replies
View Related
