Case-insensitive Search Of Varchar(max)
May 12, 2008
We have an in-house set of databases created by a member of staff who left the organisation in circumstances that mean he will not respond to queries relating to his work here. The programs he produced whilst in our employ are compiled and we have no access to the code, or the tools that he used (don't ask).
The programs allow the user to limit views based on various fields, but not the most useful field, a "memo" type field containing a textual description of work requests.
I only have Delphi 7 to use for the program development and have been using ADO, but my problem is I need to perform a case-insensitive search of a varchar(max) column based on text entered by user, but have been unsuccessful using LIKE and UPPER (which it appears you cannot use with a varchar).
Can anyone provide suggestions for what I am sure is a rather mundane and easy task for all of you.
Please note that I have only the Delphi, and no direct access to the SQL management tools.
Many thanks
Rob
View 5 Replies
ADVERTISEMENT
Apr 25, 2006
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
View 2 Replies
View Related
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Aug 17, 2005
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies
View Related
May 29, 2008
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
View 5 Replies
View Related
Aug 19, 2007
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies
View Related
Aug 29, 2000
Is it possible to change this option once SQL Server has been installed or is it only available to be set when installing ?
thanks
View 1 Replies
View Related
Nov 21, 2006
Sqlserver is case insensitive in this way ?for example:select....where col1 like '%BURG%'will return 'Burger King' ???!!!
View 9 Replies
View Related
Jul 20, 2005
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies
View Related
Jul 2, 2004
After all the pain I've been going through with code pages and collation, I was asked how, when sql server does it's joins and predicate searches, how does it actual (internals now) know the an "A" = "a" in an insensitive search?
I didn't have the answer.
Damn, Now I really have to pick up Kelans book.
View 14 Replies
View Related
Feb 2, 2006
Is there an easy way to get the Lookup component to ignore case?
View 1 Replies
View Related
Nov 22, 2006
set MS SQL 2000 case-insensitive
View 1 Replies
View Related
May 19, 2007
I have a table in MSSQL 2005 Express that stores user data. I would like to maintain the cases of user names, but I need to insure that they are not duplicated using different cases. Is there a way that I can create a constraint to enforce this?
Thanks,
WBuik
View 4 Replies
View Related
May 22, 2007
Hi:
When I import data from multiple excel files, the Excel source gives validation errors if the case of a column name changes.
How can I make my excel source Case Insensitive?
TIA
Kar
View 5 Replies
View Related
Jan 22, 2008
Hello Experts,
I tried to retreive an input column using GetVirtualInputColumnByName, to map it with output, it gave error. I found that it is because the value passed bstrName parameter was in different case. say for example in the column it is "ColNo1" where as in the GetVirtualInputColumnByName i used "Colno1".
Can anyone tell, Is there anyway to find the column in a case in-sensitive way?
Thanks.
View 1 Replies
View Related
Jun 30, 2015
If I make a search for instance
"Candy" in where statement I retrieve 12 hits however if I write "candy" I retrieve 0 hits.
Is it possible to make the database or table to be case insensitive?
View 2 Replies
View Related
Jul 20, 2005
My SQL Server 2000 does not use the accent insensitive collation setting(collation containing _AI) in full-text serches:While SELECT * FROM <table> WHERE <column> LIKE '%a%' returns 'Mäuse',SELECT * FROM <table> WHERE CONTAINS(*, 'a') does not.Setting 'default full-text language' to neutral (0) does not help.How can I make full-text searches accent insensitive?Thanks for any ideas!Matthias
View 3 Replies
View Related
Aug 21, 2015
I don't quite understand what I am asking for so hopefully this is enough to get an answer or some explanation.
Using SQL2014 I need to use a Chinese collation. I have been told that even with a Chinese collation Latin characters are there. Is there a Chinese collation that will provide Latin case-insensitive behavior?
View 4 Replies
View Related
Jul 7, 2006
Users were able tolog on to our SQL 2000 servers with their passwords being case-insensitive.
Now with SQL 2005 some users can't logon because SQL 2005 passwords are case-sensitive.
Can SQL 2005 be configured so that passwords are NOT case sensitive???
Thanks!
View 6 Replies
View Related
Apr 6, 2008
i'm doing
cast(month(dateofapplication) as varchar)
but for this month it returns 4
how can i get it to return 04 for all months of 1 character?
i'm doing this within my select statement
View 5 Replies
View Related
Nov 25, 2007
Hi,
I try to create a string that represant a insert statement but I've some problems to concat :
Set @Insert = 'INSERT INTO DEMANDES VALUES (' + @AvionID + ',' + @DemandeID + ',' + @Consommation + ',' + @Vitesse + ',' + @TailleReservoire + ',''' + @PlageHoraireDebut +''','''+ @PLageHoraireFin+''','''+ @VilleDepart+''',''' +@VilleDestination+''');';
AvionID is a int
DemandeID too
Consommation too
Vitesse too,
TailleReservoire too,
PlageHoraire varchar and ville too.
Can you explain me how to concat these strings?
Thanks for your help!
View 5 Replies
View Related
May 19, 2004
Hi all,
I wonder if it is possible to use case sensitive varchar comparsion in a query?
As I have tried and the varchar comparsion is non-case sensitive.
Thanks
ME
View 5 Replies
View Related
Mar 7, 2008
CREATE TABLE #TEST (Keyfield varchar(30) NULL)
INSERT INTO #Test (keyfield) VALUES ('M-S Logistics');
INSERT INTO #Test (keyfield) VALUES ('Monster Racing');
INSERT INTO #Test (keyfield) VALUES ('Mueller Farms');
DECLARE @Search AS nvarchar(30), @Search2 AS varchar(30)
--Query 1
SET @Search = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search;
--Query 2
SET @Search2 = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search2;
-- Why does query 2 return different result than query 1
View 3 Replies
View Related
Aug 21, 2001
Hi all,
There is a requirement to perform a case-sensitive search
on a column in table. The installation of SQL Server is
case-insensitive...
Eg.: select * from t1 where c1 = 'abcd'
should return only rows where c1 = 'abcd' and not 'ABCD'
or 'Abcd' or any other.
I understand that this can be done using the CONTAINS
predicate using Full-text indexing.
select * from t1 where CONTAINS(c1,'abcd')
Is this the right solution to the problem? Has someone
had experience implementing this?
Thanks in advance.
-Praveena
View 1 Replies
View Related
Nov 21, 2001
I'm using a 'searched case' however I don't care which method I use.
I'm open to using anything....
I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.
-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.
TIA
JeffP...
View 2 Replies
View Related
Jul 20, 2005
Hi,I have yet to find an answer for this:I want to do a case-sensitive query using "like" on a table in sql 7.Currently, "like" performs case-insensitive query.I understand that you can use the following query in sql 2000:SELECT *FROM table_xWHERE col1 collate SQL_Latin1_General_CP1_CS_AS LIKE '% AVE %'However, is there a similar method for sql 7?Any answer would be appreciated.Thanks,Jay
View 1 Replies
View Related
Mar 28, 2006
say, in my database there're two rows,
name age career
------------------------
dave 20 student
Dave 20 student
if i use select * from db where name='dave'
both will come out, how to do a case-sensitive that i got only the 1st row ?
View 8 Replies
View Related
Jul 20, 2005
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
View 4 Replies
View Related
Sep 14, 2005
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies
View Related
Feb 28, 2008
Hi ALL,
How could you make the SQL Select statement query case sensitive, what i mean is i have this select statement below ...
Select * From Staff WHERE Staff.Staff_ID = 'KabirJ' AND Staff.Password = 'KaBir1!'
and a data row in the staff table that is the following information
Staff_ID Password
Kabirj kabir1!
Since the cases in database is different then in the string provided, i want sql to return me nothing, How do i go about and achieve that???????????
Thanks
Kabir
View 2 Replies
View Related
Jun 15, 2015
Select * FROM TAB1 wher AI_TEXT_VALUE like '%PE%'
The unfortunate thing is i get entries like <Perennial allergic rhinitis>
I am looking specifically for the term PE ( All upper case ) But keep in mind, I would need to use the LIKE operator in my where clause.
( just like in the example above )
View 7 Replies
View Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
