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
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.
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?
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 ?
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.
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!
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:
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
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
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
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.
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!
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.
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.
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
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 ' '.
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'
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 ?
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)
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.
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.
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.
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???
