Searching In A Varchar Field
Jul 12, 2001
I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........
Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.
Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??
I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?
Thanks
Sumit.
View 4 Replies
ADVERTISEMENT
Jan 12, 2006
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies
View Related
Feb 25, 2008
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
View 4 Replies
View Related
Feb 22, 2008
I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'
will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'
will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.
View 17 Replies
View Related
Jul 26, 2007
Hi all,
I have a very big database with me. This database has large number of tables and each table has n number of fields.
My trouble now is that I desire to search for a field name called 'empname'. Is there any query possible for me to search all tables and return me the table names which contain fields which match with 'empname'. Adding essence to the porridge I would like to know if I specify something like '%pnam%' it should match any field name which contains 'pnam' as a portion of its name. Is all these possible ??
Thanx for any help in advance,
Regards,
tvks
View 6 Replies
View Related
Dec 5, 2007
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
View 5 Replies
View Related
Aug 29, 2007
Hello,
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
UPDATE myTable SET field = CAST(field AS int)
View 2 Replies
View Related
Sep 10, 2006
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
View 1 Replies
View Related
Mar 10, 2005
I'm using the .NET Framework 1.1 together with SQL Server 2005 Beta.
I have a search page where you can search a db using a number of fields or combination thereof. Three are text fields (patient id, surname, forename) and I can do any search using one criteria or any combination without any problem.
However, I'd also like to search on a dob (datetime field in SQL Server) and I'm having real problems in making this work. The dates are stored in the following way in SQL Server: '1938-05-31 00:00:00.000', whilst they are displayed as 'dd/mm/yyyy' on a web page, which I guess corresponds to my regional settings.
My code is shown below. I 've tried any number of combinations for the dob string with no success. I do not get any error, just no records are returned.
Does anyone have any idea what I'm doing wrong and how to correct it before this drives me crazy?!
TIA for any help.
P.S. chxPatID, chxSurname etc are all checkboxes.
<code starts>
If chxPatID.Checked Then
strSQL = strSQL & " patid= '" & txtPatID.Text & "' "
strAND = " AND "
End If
If chxSurname.Checked Then
strSQL = strSQL & strAND & " surname ='" & txtSurname.Text & "' "
strAND = " AND "
End If
If chxForename.Checked Then
strSQL = strSQL & strAND & " forename='" & txtForename.Text & "' "
strAND = " AND "
End If
If chxDoB.Checked Then
strSQL = strSQL & strAND & " dob= " & CDate(Year(txtDoB.Text) & "-" & Month(txtDoB.Text) & "-" & Day(txtDoB.Text)) & ""
End If
</code ends>
View 1 Replies
View Related
Dec 2, 2003
I need to find a better way to find all parts from a particular category. Each part can appear in multiple categories.
Currently, I use the id of a catagory and search the txtCatagory field in the position of the id.
I have included the query string for you to see.
SELECT * FROM tblParts
WHERE SUBSTRING(txtCategory,@CatId, 1) = '1'
ORDER BY PartNum ASC
txtCategory is Data Type Text
This currently does work, however is causing some performance problems with my ISP.
Any help would be greatly appreciated.
View 1 Replies
View Related
Feb 27, 2008
Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima
View 7 Replies
View Related
Feb 9, 2006
Hi everybody,
is it possible using SQL Server search for a word on each
table on each field?
Thanks
Fabio
View 6 Replies
View Related
Jan 12, 2006
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View 3 Replies
View Related
Jul 26, 2007
Hi
We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?
The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?
Please advise, thanks for your help!
Li
View 4 Replies
View Related
Feb 14, 2006
I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)
SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
View 7 Replies
View Related
Mar 28, 2007
Hi guys
I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message.
I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
Thanks
-Sarah
Select SUM(Convert(int, calculatedValue))
from monitor.dbo.monHistory
where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
This is the error message I recieve:
Conversion failed when converting the varchar value '274.2' to data type int.
View 6 Replies
View Related
Mar 23, 2008
After reading Dan Guzman's blog entry (http://weblogs.sqlteam.com/dang/archive/2008/02/21/Dont-Bloat-Proc-Cache-with-Parameters.aspx) I started modifying some of my code to try it out and ran into a stumbling block. What size would you specific for a varchar(MAX) field?
Since a varchar max field can hold up to 2 billion chracters I really don't think I need to specify 2 billion as the size. Anyone have any ideas?
View 2 Replies
View Related
Nov 21, 2000
What is the max field length in SQL Server 7.0 that a varchar field can be?
I think 8000. Please advise
Thanks
View 2 Replies
View Related
Oct 4, 2004
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement?
Thank you.
View 4 Replies
View Related
Oct 4, 2004
I would like to append information to a varchar field with an update statement, for example the field currently contains a name (Mark) and I would like to add information to name for business purposes, to update it to Markqw215, is this possible to do with an update statement?
Thank you.
View 1 Replies
View Related
Jan 17, 2005
I'm importing data from a text file into SQL as a varchar, and I'm leaving it a varchar in its final destination table. It is essentially a price, i.e., $25.65. I'm using this price field (varchar) to perform a calculation...
Everything seems to work OK, but I'm not sure about using this varchar field to perform this calculation. Is this doable, or should it absolutely be converted to say, decimal?
View 1 Replies
View Related
Jun 12, 2008
Hi guys,
How do you convert a date varchar field which looks like 20080525 to 2008/05/25?
View 3 Replies
View Related
Feb 1, 2007
What's the maximum number of characters that varchar field will take? I read somewhere that it's 8k, but I may have misunderstood.
I have to potentially store 100k+ chars in a field.
What's the best way to do this?
Thanks,
--PhB
--PhB
View 1 Replies
View Related
Jul 20, 2005
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
View 3 Replies
View Related
May 23, 2006
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
View 1 Replies
View Related
Nov 21, 2004
Hello Pros
I need to insert data in a varchar column to that when its displayed new lines will appear in the text, that is the field will hold text with multiple lines...
what's the most efficient way to do it ??
Any help will be highly appreciated,
View 1 Replies
View Related
May 26, 2005
Hi,I am using MSDE together with Enterprise Manager.I have a table with a field named description.This field will be filled by a web forms's textbox web control.The textbox's maxsize attribute is set to "3000" characters.What size do I have to adjust for my DB field description?Is the size of 3000 in Enterpise Manager equal to 3000 characters for the textbox?I am just trying to avoid errors if MSDE cuts off the string that comes from the textbox webcontrol.
View 4 Replies
View Related
Jun 30, 2005
I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric
View 3 Replies
View Related
Oct 24, 2001
I have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
Thanks!
View 2 Replies
View Related
Aug 16, 2000
I am trying to change a text field into a varchar 8000.
I get his error message when trying to convert.
Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or
to select only those that are over the 8000 character mark?
Please advise
Thanks
Susan
View 2 Replies
View Related
Feb 28, 2001
Hi,
I have to change a due_date field with a varchar(10) to a datetime. I created a new table with datetime but when I imported data into the table it errored out due to the datatype. A varchar(10) going into a datetime(8).
Is there way to resolve this without losing data?. or how do you result this problem?.
Thanks for your help,
Jeff.A.
View 1 Replies
View Related
Nov 29, 2001
Hiya!
I'm having a problem updating a varchar(4000) column in Enterprise Manager when I open the table and try to update one particular row directly. Most of my rows have approx. 100-500 characters in this column, and I can update all of them them, but one row which has 1976 characters in this varchar column doesn't let me change data by typing directly into this column, although it will let me update other columns in the same row. Altogether, my row size has a max. of approx. 5000, below the 8060 limit, so I doubt that's the problem. What Is?
Thanks,
Sarah
View 1 Replies
View Related
Feb 2, 2005
Hi, all
I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when
I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share?
Thanks
Betty
View 5 Replies
View Related
