.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

where like on varchar(500)

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :ASP.Net

I have a query which slows down immensely when i add an addition where part

which essentially is just a like lookup on a varchar(500) field


and (xxxxx.yyyy like '% blahblah %')

I've been racking my head but pretty much the query slows down terribly when I add this in.

I'm wondering if anyone has suggestions in terms of changing field type, index setup, or index hints or something that might assist.

any help appreciated.

sql 2000 enterprise.

View Complete Post

More Related Resource Links

Error converting data type varchar to numeric.


I create a form to record data using ASP.NET and SQL database...
when i run the project show error "Error converting data type varchar to numeric"

what's the problem actually.i try to change in datatype in database..
but still showing error...


Varchar string to int - how to convert empty cells to nulls

Hi, I have a large table from which I want to convert certain columns from varchar to int while loading the table to another table. However, I run into problems when the input column contains empty cells. The SSIS data conversion data flow item can't handle these so I tried the script component. With the script component I can convert these empty cells to zeros but after that I'd need to use SQL update to update the zeros to nulls which in the case of a large table with over 3 million rows takes unnecessarily long. So, is there another way?

The data types varchar and date are incompatible in the add operator

On the following query I keep getting the error 'The data types varchar and date are incompatible in the add operator'.  As you can see I have tried to convert the date but doesn't seem to work.  The variable @Date1 will start off as a date.  Any suggestions? ' declare @Date1 date declare @SQL1 varchar(2000) set @Date1 = convert(varchar, cast(getdate() as date), 102) set @SQL1 = 'select * from tbl_1 where convert(varchar,cast(tbl_1.Current_dt as date),102) = '+@Date1+'' print @SQL1    LISA86

Replace string in xml without converting to varchar using SQL Server

We have a xml datatype in SQL server which gets a xml file loaded into it. We want to replace some of the content in the xml datatype, but are running into problems converting from xml to varchar(for manipulation purposes), back to xml to output. Does anyone know of a way to replace xml without converting to varchar?Thanks in advance


Hi Folks, I downloaded SQL 2008 and installed recently.Everything perfectly works except one thing that I can't use VARCHAR AND CHAR data types anymore.I was using SQL 2005 and I had the same problem there as well. Please helpe to solve this problem

can't change varchar(13) back to a datetime.

SQL Server 2005  I had a date field that was acting weird.  I decided it didn't really need to be a datetime type and changed it to a varchar(13) which it happily let me do.  Opps, I really do want it to be a date time field...  But now it won't let me take it back.  Oddly, it also won't let me change the data in the field, or delete the data in the field.  When I try to change the data I get the following... String or binary data would be truncated.  The statement has been terminated. When I try to modify the field I get the following. first i get a warning... - Warning: Data might be lost converting column 'EODate' from 'nchar(13)'. then i get an error when I say yes 'ExecutiveOrder' table - Unable to modify table.   Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. Ok, I'm not the sharpest tool in the shed...  Any suggestions?  Thanks for your help.

auto increment of primary key which is varchar


hi everyone ...

i  was given a form in which empid is varchar  and i m supposed to auto increment it using a  function and calling it ....

i have never done this. plz help me out the way .

Varchar to Numeric



I have a textbox where I can enter a number. If the number exists in my sql database then it would change the label accordingly. It seems to work with any input number except for numbers starting with zero. If I input a number that is 12 characters or more and  starting with zero(089470211000) do not get an error. If it is 11 characters or less  and beginning with zero (I get the error: Arithmetic Overflow error converting varchar to data type numeric.

Imports System.Data.SqlClient
Public Class ValidateForm

  Private Sub MineralCloseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MineralCloseButton.Click
  End Sub
  Private Sub CheckButton_Click(

Error converting data type varchar to numeric



I am trying to execute below query.



CAST('123,456,789.00' AS DECIMAL(11,2))

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Cash' to


Line 64:         CMD = New SqlCommand(SQL, CON)
Line 65:         CMD.Parameters.Add("@payment", SqlDbType.VarChar, 50).Value = payment.Text.Trim
Line 66:         CMD.ExecuteNonQuery()
Line 67:
Line 68:

Hi All,

I'm having the above mentioned error message.

Please help.






SQL Server 2008 multiple lines in a varchar(MAX) column?



I'm using varchar(Max) to store a text file content. It's not taking the newline character. I tried \n and char(13) etc.. whatever solution posted in the net.

ex : update test set f='This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.' 

after updating it doesn't have any newline or linebreak. Can anyone help me in this how to have linebreaks in the varchar(max) field.

Thanks a lot.

data type with Varchar replacement


Hi all

I was using derived column for  replacing null values with datatype into value 0 , varchar into '$$'. Now I would like to replace with data type Varbinary.Can any one suggest replacement with which value.

I am passing the '0' for datatype int and '$$' for datatype varchar. can any one suggest for datatype varbinary what value I have to pass?

Use of VarChar(8000) vs. VarChar(7500) with SQL Server 2000


I have heard that even though the maximum varchar size is 8000, that we should not go higher than 7500 for the size value.

What is the logic behind this advice?


Using Cast and Sum on VarChar Column


Okay, so I have a little problem developing a SQL Select Statement on a column that is formatted pretty badly. Firstly, the Database has already been created and filled with Data in a Test Environment, I'm not sure if the format is the same or has been updated in Production but since I'm only able to work with the Test Environment right now I'm stuck with using what I have. The Column in one Table shows a Snapshot of a clients account which is set to VarChar(50) but the actual data in the column is formatted like a money format (with a $ and decimal point so for example "$5,000.00" would be an entry in one Row). 

What I'm trying to do is a create a WebService that will be used in a iPhone Application and one of the Methods needs to just give a quick display of the total value of all Clients with each specific company but I can't perform a SUM function on a VarChar column and I've tried to CAST and CONVERT the column into money, int, decimal and just about every format I can think of but I keep getting an error stating that I convert the data type varchar to which ever one I'm trying. I think it has something to do with the $ already listed in the Column, so I wanted to know what I could do to either remove the $ sign (like a Trim option) or if there is a better way of doing this that I can't think of.

How to convert varchar to = , > and < operators

I have a procedure like

create procedure emp_fill(@operator1 varchar, @operator2 varchar, @emp_salary int, @emp_comm int)
select * from employees where salary [>|=|<] @emp_salary and commission_pct [>|=|<] @emp_comm;

Now the problem is I am sending @operator1 and @operator2 parameter in this procedure either '>' or '=' or '<'
But I cannot write select * from employees where salary @operator1 @emp_salary and commission_pct @operator2 @emp_comm;

What I have to do to convert @operator1 and @operator2 which are varchar type, to change them as [>|=|<] operators and use them in the procedure.

pls help

CLR Stored procedure SQLString (NVarchar) to varchar.


Hi trying to upgrade my current SQL 2000 database to 2008.

All the legacy code uses VARCHAR: tables columns, variables, input/output of SPs and XPs etc...

I'm trying to port an existing extended stored procedure to CLR stored procedure. The output parameter for the old xp is varchar(8000). In CLR it is obviously SQLString which maps to NVARCHAR(4000)

Now the legacy code which is expecting VARCHAR(8000) output is calling the new CLR which returns NVARCHAR(4000).  The CLR procedure can indeed return more then 4000 characters. I can use SQL facet size of -1 to force NVARCHAR(MAX). But I hear the performance is slow. Also what are the consequences of a CLR returning NVARCHAR to an SQL SP that expects VARCHAR?

Is there a way to return larger string from a CLR to SQL code that expects varchar?

This is the error I'm getting

Msg 6522, Level 16, State 1, Procedure xp_XYZ, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "xp_XYZ":

System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 8000 bytes to a T-SQL type with a smaller size limit of 8000 bytes.


 at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

 at Syste

"IN" operator and error converting data type varchar to numeric



I hope someone can help me figure this out please...

I have this query that returns some student data - specifically the student's names, and some classes they're enroled in. What I'm trying to do is return multiple rows using the LIKE operator.

However when I run it and plug in the parameters I get the errror - "Error converting data type varchar to numeric". This error doesn't occur when there is only ONE studentId. Only when there are multiple studentIds. I know it's to do with the string of studentIds being passed, but I'm not sure how to fix it?

Should I be using varchar as the datatype to pass the studentIds? Or do I need to cast this as something else?

Any ideas, much appreciated.



	@StudentIds varchar(8000),
	@ProfileId numeric,
	@Fname varchar(50) OUTPUT,
	@Lname varchar(50) OUTPUT,
	@ClassName varchar (100) OUTPUT,
	@ClassName2 varchar (100) OUTPUT,
	@ClassName3 varchar (100) OUTPUT,
	@ClassName4 varchar (100) OUTPUT,
	@Class numeric OUTPUT,
	@Class2 numeric OUTPUT,
	@Class3 numeric OUTPUT,
	@Class4 numeric OUTPUT


@Fname = Fname, @Lname = Lname, @ClassName = C.ClassName, @ClassName2 = C2.ClassName, @ClassName3 = C3.ClassName, @ClassName4 = C4.ClassName,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend