Problem With PATINDEX Function For Case-sensitive Information
Aug 21, 2007
Hi,
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
I have a number of columns I'm selecting from a table, and I'd like to use PATINDEX to search for a string within a field. Upon finding this string it would return another string...:
SELECT CASE PARTNERID WHEN PATINDEX('%INX%',PARTNERID)>0 THEN 'AXXENT' ELSE PARTNERID END AS PARTNERID
However, I'm getting this error:Incorrect syntax near '>'.
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?
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 ????
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?
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!
Hi all, My database is not case-sensitive, but I want output like... SELECT patindex('%[A-Z]%','gaurang Ahmedabad') The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1. Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression). Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this? Thanks, Gaurang Majithiya
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?
selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution sqlConnect.Open();
//instantiate the SqlDataReader reader SqlDataReader loginReader = selectLogin.ExecuteReader();
//try and catch SqlException error try { if(loginReader.Read()) {
// check whether the user is the role of administrator or operator // I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page. if (loginReader.GetValue(1).ToString().ToUpper().Equals("O")) { Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");
} else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A")) { Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx"); }
}
else { //clear content of textbox and display error message txtId.Text=""; txtPwd.Text=""; lblLoginFail.Visible = true; lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!"; }
} catch (SqlException se) { if (se.Number == 17) { lblLoginFail.Visible = true; lblLoginFail.Text = "Could not connect to the database"; }
I want to change my database character set and I use database SQL Server 6.5. At the first time I install database, I use charset type to case sensitive. Now, I want to change this charset from case sensitive to un case sensitive.
I hope somebody want to trasnfer knowledge about it. Thanks for attention.
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not! How can I set my SQL Server 2000 to be case sensitive as well?
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?
Our database is configured as case insensitive. I need to run a query which is case sensitive. Is there a query option or function I can use to compare, taking upper/lower case into consideration?
How do you set the case sensitive option in SQL 2000? If l already have data in the database whats the best way of doing this.l want the selects on the database to be case sensitive?
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'm a bit flummoxed on this one (doesn't take much these days). We have a test SQL database and a prod SQL database configured with the same user name and a complex password.
The password consists of letters and numbers, mixed upper and lower.
What I am seeing, however, is that SQL will permit access even if the user gets the case wrong on the letters. One letter, two letters, all letters. It doesn't matter.
I thought SQL passwords were case sensitive; was I wrong?
Regards,
hmscott
Edit: I should add that I am running SQL 2000, SP3a (hotfix 0818) on Windows 2000 SP4 and that SQL is clustered on two servers in Active/Passive mode (this applies to both Test and Prod).
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
Using ADO 2.7, what is the best way to perform a case-sensitive filter? I have seen on other forums where folks have said that the StrComp function can be used inside of the .Filter method, but I haven't been able to get that to work. I am using VB 6 and ADO 2.7, and have a need to perform case sensitive filters. I know I am not the ony one who has needed to do this...
In the process of migrating from SQL Server 2000 to SQL Server 2005, an application that auto-generates reports suddenly started failing. It turned out that the usernames and passwords were being up-cased, and SQL Server authentication on the 2005 box was rejecting the logons. When the app was changed to leave credentials in whatever case they were received, the reports all ran successfully.
Is that case-sensitivity a function of the collation schema on the database? On the entire SQL Server installation?
Either way, how can it be changed to be case-insensitive for such credentials?
I have an application that at the begining a user will login with a user name and password which is stored in the database. The SQL statement is as follows:
"SELECT id_employee FROM employee WHERE employee_number='" + txtUserName.Text + "' AND passWord='" + txtPassword.Text + "'";
For testing purposes I have set the password to the word test. The problem is, if the user enters in TEST or TeSt or TESt it will grant them access. How do I set it to force the correct case?
