I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?
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
Hello!
I am trying to run a query on an SQL-server database:
SELECT (Left(Comments, 20)) As shortComment FROM myTable
The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...
How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?
noccy
I want to insert a value which has the UTF-8 encoding into a field of the database which has the "text" data Type
but it saves like this : " ?????????????? "
I would be thankful if u tell me how should I save it ?
here is the SqlComand I wrote :
string comand = " Insert into Table1 ( title , Body ) values ( ' " + textBox1.text + " ' , ' " + textBox2.text + " ' ) " ;
Table1
=============
title nvarchar(1000)
body text
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies View RelatedHello 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 !
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
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()
...
...
...
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 '''%') + '%'
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...'?
I want to perform Replace(searchstring, oldstring, newstring) like function in T-sql as same as in VB..
is there existing one or any other user-defined function?
thanks...
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.
hello all!!! how are you all?
View 3 Replies View RelatedHi again and again. I have problem on varchar coz it cannot handle more than 8000 characters. Is there any datatype to use instead of varchar? I tried to use ntext and text but they are not allowed to use in query analyzer.
DECLARE @religion varchar (8000)
set @religion = 'aaasdsassssss...........'
thanks.
-Ron-
Let's try this again.
When I try to use the replace function in my query it ignores the "<" and ">" characters, so that this query:select Description, replace(Description, '<', '* * *') as [Description 1] , replace(Description, 'br /', '? ? ?') as [Description 2] from TT_Projects
produces these results:
Description | Description 1 | Description 2
testsfv <br /> | testsfv <br /> | testsfv <? ? ?> I also tried to replace char(60) with no success.
Any ideas why this is happening?
Hi,
I posted a request here and am still working on it when I landed on this bug.
select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table
The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..
Is this a mssql bug?
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters.
Is there any way to solve my problem??
Jaimin
Hi,Please find the below scenarioOriginal Tablecourse branch_existBY0UI1PO1LI0MK1select REPLACE(branch_exist,1,'yes') as branch from university;displayscourse branch_existBY0UIYesPOYesLI0MKYesWhat i need iscourse branch_existBYNoUIYesPOYesLINoMKYesSql-Server replace function only accepts three arguments, anysuggestions will be greatly welcomed!
View 3 Replies View Related
Hi,
We have this syntax from Microsoft for Replace function:
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
I want to search for two keywords and not only one:
images and images/ (second one is with back slash.
Is it possible to include something like:
images/ OR images in expression2 like
REPLACE ( string_expression1 ,'images/ OR images', string_expression3 )
I want something like that, how do I get that please?
Thank you.
I have 2 tables
1) One With ExchangeRates
a. CurrencyCode ValidFromPeriod Rate
b. EX1 200801 1
c. EX1 200803 2
d. EX1 200805 3
2) One with just OrderHeaders
a. ID CurrencyCode Period
b. 1 EX1 200801
c. 2 EX1 200802
d. 3 EX1 200803
e. 4 EX1 200804
f. 5 EX1 200805
The Idea is to link these orderheaders with their correct ExchangeRate. Which I only managed to do by using a function. Is there a beter (faster) way to do it?
Code Snippet
SELECT OrderHeaders.ID, OrderHeaders.CurrencyCode, OrderHeaders.Period, dbo.GetExchangeRate(OrderHeaders.CurrencyCode, OrderHeaders.Period) AS ExchangeRate FROM OrderHeaders
FUNCTION GetExchangeRate
@CurrencyCode VarChar(3),
@Period int
BEGIN
DECLARE @VarResult Float
SET @VarResult =0
SELECT TOP 1 @VarResult=Rate FROM ExchangeRates WHERE CurrencyCode=@CurrencyCode AND ValidFromPeriod<=@Period ORDER BY ValidFromPeriod DESC
Return @VarResult
END
Hello there!
I installed and configured SQL Server 2005 reporting services. When I try to connect to it using SQL Server Management Studio, I get the following error:
Client found response content type of "text/html", but expected "text/xml".
What should be done to overcome this?
Does anyone have any idea about this?
Thanks in advance
Hemant
Hi,
In my VB .Net application , when I am trying to fire a SSRS report on my local machine . Its giving the error ".Client found response content type of 'text/html', but expected 'text/xml'."
The request failed with the error message:
--
<html><head><title>Error</title></head><body>The Local Security Authority cannot be contacted
</body></html>
--.
the error is being thrown at the code line...
reportviewer.serverReport.getParameters()
I have reportserver as
https://ReportServerforApplication.abc.com/abc_ReportServer
I have admin rights on my machine but still the problem persists. Am i missing some user group?
Could someone Please help me with this.
Thanks In Advance
Gaurav
Hi,
I am getting these errors from Report Manager. It seems that every time after the server has been idling for about 15 minutes, I would get this error message. I can click on other links and I am able to continue to use Report Manager.
Is there a timeout setting to help me resolve this or is this a bug with Report Manager? I just insitalled SP2 on the SQL Server and the Reporting Service.
Error message on browser:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3042.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline} </style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00" target="_blank">Get Online Help</a></li><ul> <li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </ul> </body> --.
Any ideas?
Thanks,
-waslam
heyi'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like soSET @prOtherValue = REPLACE(@prOtherValue,''', '''')this doesn't work thowhats the work aroundcheers!!!!
View 6 Replies View Relateddoes anyone know where i can find a user defined function that replaces accented characters with their normal ones
i want to replace all accented characters since for some reason sql server does not store the characters properly, or when they exported to excel for html they are displayed incorrectly
thanks
i have column in table which contains tabs and " i want replace with space...i am using repalce function is thier other way to improve performance with out using replace function.
View 9 Replies View RelatedIs it possible to use the REPLACE function in SQL Server 2000 so thatit returns a string containing only alpha-numeric characters (muchlike using regular expressions)?Thank you in advance for any suggestion.Darren.
View 1 Replies View RelatedUpdate query to modify a Url in the text column with another url
Ex:
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/10325
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/10118
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/10150
to
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/11111
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/11111
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/11111
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
Hello Guys.
Here is my issue i have email addresses in a column of a table in sql server. Are addreses has changed since and i need to do a mass update of these email addreses i need to replace a few char to reflect this change like ex:
AAAAA@BBB.CCC.Com i need to replace the BBB. part with nothing so that it looks like this AAAAA@CCC.Com.
Please help.
sunil writes "hi,
i have a problem when i updating record in the database. i am trying to insert value ('Company's Director') in the field but i receive an error Incorrect syntax near 's' what do i do for this error.i want to value like as ('Company''s Director').
please solve my problem"
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).