Replace Funtion
how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?!
SELECT REPLACE(tasks, '/', '????') AS EXPR1
FROM log_descriptions
what should ???? be?
View Complete Forum Thread with Replies
Related Forum Messages:
REPLACE Funtion For Multiple Values In SP/Reporting Services
Running into a problem with trying to pull multiple values in a stored procedure. Ran Profiler for trace on what variable is coming back as when running in Reporting Services. Profiler shows this: exec apGetCompanysByRep @Product = N'Absorbent Pads,Airedale Terrier' Trying to run Replace function but not quite getting the syntaxt correct. The stored procedure will work but when running report will often get unclosed quotation marks. What I have: Set @Product= Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + '''')) This is probably a little too much coding but I've tried several ways. In the SP the code is as follows: CREATE PROCEDURE [dbo].[apGetCompanysByRep] @Magazine varchar(6) = null , @DirectsMagazine varchar(6) = null , @Category varchar(50) = null , @SubCategory varchar(50) = null --, @FirstName varchar(30) , @LastName varchar(30)= null , @Product varchar(1100) AS declare @SQL varchar(2000) Set @Product=Replace(@Product, '''' + ',' + @product + '''', (@Product + '''' + ',' + ' ' + '''' + @product + '''')) set @SQL = ' Thanks for any assistance anyone can give.
View Replies !
Seeking An SQL Funtion ~ Do You Know IT
Hi All, Please can you help. i a looking for a funtion that will be able to view all the child (many) records in a one to many relation ship and make a list of the parents names....is there such a function??? Example In the one to many contacts db below, if this mystery function ran for "bill" it would say his "parents records" are Gary and Mac Parent Table~ Child Table Gary Bill Tom Harry Mac Bill Jef Tod Charles Les Helen any help here would be much apprecaited kind regards Tony UK
View Replies !
Reg Split Funtion
Hi, Is it possible to split the following value in sql server ? I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database. Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution .. I am using ASP.Net and C# backend is SQL Server 2000. Thanks and Regards Arul
View Replies !
BCP Funtion Problem
Hi, I'm using the function bcp to transfer data from my table to a data file. When I use it at the dos command prompt, no problem everything works really well. But if I use query analyser I have an error: unable to open bcp host data-file. Here's the line: xp_cmdshell 'bcp table.owner.inventaire out c:1est.txt' The problem is not with xp_cmdshell because I can call simpler executables in query analyser.
View Replies !
To_date Funtion YYYY-DD-MM
Hi All: We have a database date column in this format: 1/27/2008 1:20:00 AM and need a date or time function to cast local JVM time into the above format. Suggest me the best options. 1. To go for a more comfortable date format in the database itself or 2. To have an appropriate conversion in my code.( I am not feeling good about the suffix AM/PM in the date column!) Thanks.
View Replies !
Round Funtion On Entire Columns In MSSQL?
Hi,I'd like to round all amounts in a certain column to 2 decimals.I tried the following query, but eventhough the syntax is correct, itdoesn't give any result:update gbkmutset bdr_hfl = round(bdr_hfl,2)can anyone help me?cheers,steve
View Replies !
SQL Replace '-' By ''
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 Replies !
Replace?
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 Replies !
Using 'IN' To Replace 'EXCEPT'
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 Replies !
How To Replace Having
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 Replies !
Using Replace
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 Replies !
Replace Roa
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 Replies !
Replace '
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 Replies !
How To Do REPLACE In SQL Queries
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 Replies !
Replace, The ' Character, And Yea...
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 Replies !
Replace &<br&> Tag In CRLF
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 Replies !
Find And Replace
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 Replies !
Replace Function In SQL?
Hi, I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name. Thanks
View Replies !
Using Replace Function
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's. I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced. Am I not using the replace function correctly? Is there another way in which I might accomplish the task? Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname" Dim CommandText As String 'Command text is greatly abreviated for this discussion. CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description" Dim myConnection As New SqlConnection(ConnectionString) Dim myCommand As New SqlDataAdapter(CommandText, myConnection) Dim ds As New DataSet() Dim dv as new dataview() myCommand.Fill(ds) DataGrid1.DataSource = ds DataGrid1.DataBind() ... ... ...
View Replies !
Find And Replace.
Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time? That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do. Thank you to whomever can help. Alec
View Replies !
REPLACE ISSUE..
Hi All,I have the following STRING - 'D or Shorty'Struggling with the syntax required to replace the 'or' with ; withoutalso replacing the 'or' in 'Shorty' with ;e.g.ORIG STRIMG:D or ShortyREQUIRED STRING:D ; ShortyThanks in advance
View Replies !
How To Run Replace On All Columns
Here is my replace query and I need to run this on every column in mytable. Right now I manually enter the column name (_LANGUAGES_SPOKEN)but this is time consuming and would like to automate this process asmuch as possible.Update PROFILESET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN asnvarchar(255)),char(13)+char(10),':')Thanks,JP
View Replies !
Replace Data Using Sql
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 Replies !
Replace Query? Possible?
Is it possible to make a replace query in SQL? So Find What, Replace With I have a table with email addresses. But now for more than 500 people it must be changed from user@company.nl to user@COMPANY2.nl Is this possible? Thanks in advance!
View Replies !
REPLACE Name If Code Like
Hello for MS SQL 2000 I want to replace a value if a Code LIKE 'A%' MyTable : ID Name Code SELECT ID, Name, Number FROM MyTable but if Code LIKE 'A%' then Name = 'LAN' how can i do that ? thank you
View Replies !
Replace Command
can the command replace use variables ie replace(@Name,'.com','.com;') im getting an error with this and i was just wondering if it was just me Jim
View Replies !
Need SQL To Replace CURSOR
Hello, I have a 3 million-row table, which has primary key that consists of id_no and addDtime. The id_no is duplicated with a different addDtime to make the row unique. I need to remove all duplicate id_no rows except the one with the latest addDtime. A cursor on a test server with 1-million rows took 14 hours before somebody switched the box off. Is there any way of using SQL to replace the cursor. Example of Cursor: Declare @id_no tid_no = "" DECLARE UPD_title_cursor CURSOR FOR SELECT id_no FROM updtitle GROUP BY id_no HAVING COUNT(id_no) > 1 OPEN UPD_title_cursor FETCH NEXT FROM UPD_title_cursor INTO @id_no WHILE @@fetch_status =0 BEGIN BEGIN TRANSACTION delete updtitle where id_no = @id_no and addDtime < (select max(addDtime)from updtitle where id_no = @id_no) if @@error <> 0 BEGIN ROLLBACK TRANSACTION END COMMIT Transaction FETCH NEXT FROM UPD_title_cursor INTO @id_no END CLOSE UPD_title_cursor DEALLOCATE UPD_title_cursor Go
View Replies !
SQL To Replace String
Hi, I need to write a single replace sql as folows: I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*' I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5' any help would be appreciated Thanks
View Replies !
SQL To Replace String
Hi, I need to write a replace sql in TSQL as folows: I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*' I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5' any help would be appreciated Thanks
View Replies !
REPLACE Function
I'm trying to replace some text in a field. It looks something like this: 63.73 Avail %= 36.27 Used space MB = 2609.34375 Free space MB = 1485.34765625 I only want the 63.73 number at the beginning. How do I put a wildcard to replace everything after it, starting with 'Avail...'?
View Replies !
Using Like Function With Replace
I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks. declare @key as varChar(50), @sql as varchar(1000) set @key='flow afow' set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%''' exec(@sql) select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%'
View Replies !
SQL Replace Apostrophe
Hello Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table. Suggestions gratefully received! Mark
View Replies !
SQL To Replace String
Hi, I need to write a single replace sql as folows: I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*' I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5' any help would be appreciated Thanks
View Replies !
Replace NULL Value
Is it possible to update NULL value? My datatype for the field is float i tried: update table1 set [value1] = '0' where [value1] = '' however it does not replace the null value. So what is the sql statement? Thanks
View Replies !
SQL Regex Replace How-To?
Anyone find any good readings on how to do a regular expression to replace text? Basically trying to figure out how to replace text in columns (replace quotes " with the word inches. so 12" will be 12 inches)
View Replies !
SQL Replace Problem
Greetings All. If i have the statment "I like widgets and my favorite widget is red." How can use the sql Replace function to only replace the the "widget" with some new word say "food" for an exact match. For example "I like widgets and my favorite food is red." without it returning "I like foods and my favorite food is red:? See the problem? I'm not sure how to do an "Exact" match, instead of substring using this fuction.
View Replies !
Doing The Same Replace On Several Columns?
In the derived column task you can choose each column and write an expression for each column. But when you need to do a <ISNULL(status) ? "0" : statusdato> on 40-50 columns it get kind of irritating. Is there a way easy to do the sam expression on a selection of columns like a sort of derived column task, where you write an expression and assign that to a selection of columns (otherwise this would be a wish :-) )
View Replies !
Using The REPLACE Function
Is it possible to replace 4 different values into one alias column? ie REPLACE(EligStatus, '0', 'Verified') AS VRFD ..now I need to replace 3 other values with words but i want to keep them in the same VRFD column so I can add it to the report layout. or is there a better way to do it? I can't set up a linking table because nowhere in the DB are the values linked to the actual status. Thanks in advance, Mark.
View Replies !
Find And Replace XML Value(SQL RDL)
Hi, When i try to execute the below code, i am getting the error: Object reference not set to an object I am getting the error at: String Str = Xnode.InnerXml; bcoz Xnode is null XmlDocument Xmldoc = new XmlDocument(); Xmldoc.Load(@"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\DEFECTS.rdl"); XmlNode Xnode = Xmldoc.SelectSingleNode("//Report/Body/ReportItems/Rectangle[@Name='ReportHeader']/ReportItems/Textbox[@Name='Label140']/Value"); String Str = Xnode.InnerXml; Xnode.InnerXml = DescriptionTextBox.Text.ToString(); Xnode.RemoveAll(); Xmldoc.Save((@"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\DEFECTS.rdl")); The XML file is <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources>....</DataSources> <Body> <ReportItems> <Rectangle Name="ReportHeader"> <ReportItems> <Textbox Name="Label140"> <Top>0.39582in</Top> <ZIndex>2</ZIndex> <Style> <TextAlign>Center</TextAlign> <PaddingLeft>0in</PaddingLeft> <LineHeight>19pt</LineHeight> <Color>#000000</Color> <PaddingRight>0in</PaddingRight> <PaddingTop>0in</PaddingTop> </Style> <Height>0.1979in</Height> <Value>="List of Critical Defects as of " + Now()</Value> </Textbox> </ReportItems> </Rectangle> .......etc What is wrong in my code? Thanks in advance.
View Replies !
Replace Does Not Seem To Work
My function keeps going into a loop. The fucntion removes html tags. It works great with everything except with this one 4,363 character string. I'm not quite sure what the problem is, please advise. Thank you. I may have a bunch of extra declarations that dont make sense to you but they were part of my debugging... Also don't let all the code distract you, the long string is just the value of the html tag I'm trying to replace with nothing. Also there are a bunch of replaces before the while loop but the magic happens in the while loop. i've assiged to @temp the only part that my html code that replace could not replace. Every other tag I could see the function removing, it would find the beginning of the tag, end of tag, substring it and replace it. but this one it would find it substsring it and was unable to replace it and comes back with "String or binary data would be truncated." and then it loops infinetly because it keeps finding the tags it could not remove. please help. thank you. also.. I'm not sure if you call can see what I see but when I review this a bunch of emoticons show up? not sure why I did not pu them there, just relate them to their correspnonding code, colon uppercase P, :-# colon dash pound, colon uppercase S. declare @temp nvarchar(max), @input nvarchar(max), @count1 int, @count2 int, @wtf nvarchar(30), @blankString nvarchar(max), @beginBlanks int, @countBlanks int, @removeString nvarchar(max), @openTagPos int, @closeTagPos int, @emailString nvarchar(max) set @temp = 'h<!--a:link {mso-style-priority:99;} span.MSOHYPERLINK {mso-style-priority:99;} a:visited {mso-style-priority:99;} span.MSOHYPERLINKFOLLOWED {mso-style-priority:99;} p {mso-style-priority:99;} p.MSOACETATE {mso-style-priority:99;} li.MSOACETATE {mso-style-priority:99;} div.MSOACETATE {mso-style-priority:99;} span.BALLOONTEXTCHAR {mso-style-priority:99;} span.BALLOONTEXTCHAR0 {mso-style-priority:99;} span.BALLOONTEXTCHAR00 {mso-style-priority:99;} span.BALLOONTEXTCHAR000 {mso-style-priority:99;} span.BALLOONTEXTCHAR0000 {mso-style-priority:99;} /* Font Definitions */ @font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4;} @font-face {font-family:Calibri;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman";} a:link, span.MsoHyperlink {color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {colorurple; text-decoration:underline;} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman";} p.MsoAcetate, li.MsoAcetate, div.MsoAcetate {margin:0in; margin-bottom:.0001pt; font-size:8.0pt; font-family:Tahoma;} span.BalloonTextChar {font-family:Tahoma;} span.balloontextchar0 {font-family:Tahoma;} span.balloontextchar00 {font-family:Tahoma;} span.balloontextchar000 {font-family:Tahoma;} span.balloontextchar0000 {font-family:Tahoma;} span.EmailStyle24 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle25 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle26 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle27 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle28 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle29 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle30 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle31 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle32 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle33 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle34 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle35 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle36 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle37 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle38 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle39 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle40 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle41 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle43 {mso-style-typeersonal-reply; font-family:Arial; color:navy;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.Section1 {pageection1;} /* List Definitions */ @list l0 {mso-list-id:223109352; mso-list-type:hybrid; mso-list-template-ids:1861408968 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level2 {mso-level-tab-stop:1.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level3 {mso-level-tab-stop:1.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level4 {mso-level-tab-stop:2.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level5 {mso-level-tab-stop:2.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level6 {mso-level-tab-stop:3.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level7 {mso-level-tab-stop:3.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level8 {mso-level-tab-stop:4.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level9 {mso-level-tab-stop:4.5in; mso-level-number-position:left; text-indent:-.25in;} @list l1 {mso-list-id:1020744073; mso-list-template-ids:-1621449136;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} -->i' set @input = @temp set @emailString = @input set @emailString = replace(@emailString,'''','''''') set @openTagPos = charindex('<', @emailString) set @closeTagPos = charindex('>', @emailString) set @emailString = replace(@emailString,'> <','><') set @emailString = replace(@emailString,' ','') set @emailString = replace(@emailString,'v:* {behavior:url(#default#VML);}','') set @emailString = replace(@emailString,'o:* {behavior:url(#default#VML);}','') set @emailString = replace(@emailString,'w:* {behavior:url(#default#VML);}','') set @emailString = replace(@emailString,'.shape {behavior:url(#default#VML);}','') set @emailString = replace(@emailString,'st1:*{behavior:url(#default#ieooui) }','') set @emailString = replace(@emailString,'’','''') set @emailString = replace(@emailString,'“','"') set @emailString = replace(@emailString,'”','"') set @emailString = replace(@emailString,'…','...') set @emailString = replace(@emailString,'–','-') set @emailString = replace(@emailString,'t./','') While (@openTagPos + @closeTagPos) <> 0 BEGIN SET @removeString = substring(@emailstring, @openTagPos, @closeTagPos - @openTagPos + 1) set @emailString = replace(@emailString,@removeString,'') --This select is just to see what is happening select len(@emailString) sizeOfString,@removeString stringToRemove, @opentagpos beginOfTag, @closetagpos endOfTag, @emailstring theString set @openTagPos = charindex('<', @emailstring) set @closeTagPos = charindex('>', @emailstring) END select @emailString
View Replies !
Using T-SQL REPLACE (or XML) To Add An Attribute To Every Other &&<td&&> Tag
I have the following as part of a stored procedure that generates an HTML report based on the data inserted into the table variable "@tblTemp": SET @html = N'<table border="1" width="0" style="font-family: Verdana, sans-serif;">' + N'<tr><th>Name></th><th>ID</th></tr>' + N'<col align="left"></col><col align="right"></col>' + CAST ( ( SELECT td = [Name], '', td = IDNumber FROM @tblTemp ORDER BY ID FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; SET @html = @html + N'<br /><br /><img src="muuscreenshot.jpg" width="452" height="507" alt="sample.aspx" />' This generates a 2 column table that later in the stored procedure is sent out via sp_send_dbmail. The problem I'm having is that I want the right (2nd) column of the table to be right-aligned. For some reason, the line " N'<col align="left"></col><col align="right"></col>' +" doesn't right-align the 2nd column when the report is sent through email (althought it does work if I take the generated string a use it to manually create an HTML file). So I want to add something like: SET @html = REPLACE(@html, '<td', '<td align="right"') but I still want the left column to be left-aligned, and this right-aligns everything. Is it possible for me to use REPLACE (or some other string function) to replace every other (alternating) "<td", or should I be looking at parsing this string using some 2005 XML features (unfortunately, using a CLR function to do this is not an option, since I am not allowed to turn that on on the server I'm using)? Thanks in advance. -Dave
View Replies !
Replace() Only One Instance???
Could anyone help me here I have products in a table, but I need to select a distinct list, the list contains the following: Contract Purchase Advance Advance Hire Purchase Advance Advance Hire Purchase Arrears Finance Lease Advance In the case of the first couple of lines I need to replace only 1 instance of 'Advance' not both. Is there anyway I could do this, I know the Replace() function does not replace only one instance, i.e. Select Replace(ProductNames, 'Advance', '') from ProductList Would removed both instances, could anyone help me please? Thanks in Advance, Steve
View Replies !
Search And Replace?
I have a notes table in which I have 10,000 rows. Unfortunately some of the notes are compressed together as one long string. I can fix the issue if I can do a search and replace on ';' and replace with '; ' is there a way to do this?
View Replies !
Replace Apostrophe In SQL
replace(lastname," ' '",'"x"') (spaces for clarity only) Result: Invalid column name ' ' '. How do I get around the invalid column name? Thanks in advance for your assistance!
View Replies !
Replace Problem
Hi.I have a column wich has to be stripped. I wrote a query like this: UPDATE tbl_product3 SET product_stripped = REPLACE(product_name, ' ', '-') it works fine.but the problem is if the space is at the beginning of the product_name I dont want to replace '-' at the beginning of the product_stripped. what sould I do to have the correct results. " 170 caps" the result is "-170-caps" but I want "170-caps"(no - at the beginning) thanks
View Replies !
Replace The Xml Node
Hi , CREATE TABLE KUMAR ( ID INT PRIMARY KEY, Document XML(CONTENT KUMAR)) --Document is the xmlcolumn --------------------------------------- CREATE XML SCHEMA COLLECTION KUMAR AS 'schema file to be placed' --KUMAR is the XML-Schema collection name -------------------------------------------- INSERT KUMAR VALUES (1,'XML FILE TO BE PLACED') When I try to update the xml data by using the below query UPDATE KUMAR SET Document.modify('replace value of (/X12_U1_837/UserId) with "ABC"') --(/X12_U1_837/UserId) Contains the XYZ,Which i need to replace with ABC I am getting the following error XQuery [KUMAR.Document.modify()]: The target of 'replace' must be at most one node, found 'element(UserId,#anonymous) *'
View Replies !
Convert And Replace
cast([Birthdate]as datetime) [Birthdate], 01/12/2001 I have created this query below to change it to the format mmddyyyy. I not sure were the birthdate goes at in this query. replace(convert(varchar(10), getdate(), 101), '/', '')
View Replies !
|
TRACKER
