SQL Select Statement With Case SENSITIVE
Hi,
I am wondering how do I write a query that will take case sensitive into consideration. For example, I have "ABCD", "abcD", ABcd", "AbCd" in a table. I want to retrieve "AbCd" only from the table. This is the sql statement that I have:
sqlSelect = "SELECT * FROM [al_table] WHERE [alphabet]= @alpha"
Dim selectCmd As New SqlCommand(sqlSelect, myConnection)
selectCmd.Parameters.AddWithValue("@alpha", "AbCd")
From what I have it will return all those value. But that is not what I want.
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
Case Sensitive Search In Sql Select Statement?
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 Replies !
How To Make Select Statement With &"case Sensitive&"
Hi, I am using SQL Server 2005 now. I have to queries.that are select 8 from testdb where name = 'TONY' select 8 from testdb where name = 'tony' the two queies get same results now. but 'TONY' and 'tony' are different if we need 'case sensitive'. how to make the above queries get different result My test data like following (testdb table) name phone TONY 12345 tony 67890 Thanks Mark
View Replies !
Is The Sql Delete Statement Case Sensitive?
I need to delete a record from a SQL server database table. I need to target a specific column with a query that looks like the following: delete from NameTable where Name = 'Barbosa' The problem is that their are two other records with the name spelled similarly, only differing by case sensitivity. Like as follows: 'Barbosa' 'barbosa' 'BarBosa' Is sql case sensitive? Will my Sql delete statement only delete the record with the word 'Barbosa' in that column. Or will it delete all of the records with the names 'Barbosa' 'barbosa' 'BarBosa'
View Replies !
How To Select A Case Sensitive Value In SQL With C#
Hello, 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? I am using SQL 2005 for the database. Thanks! ~zero
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
HELP! Case Insensitive Database On Case Sensitive Server
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 Replies !
CASE In Select Statement
Hi, i've got this stored procedure: @LidFederatieNummer varchar(20), @ClubID int, @LidClubNummer varchar(50), @PersoonNaam varchar(100), @ClubStamnummer varchar(10), @ClubNaam varchar(50), @SeizoenID int, @ShowAllJN bit, @LidFederatieJN int, @CategorieID int AS BEGIN IF @CategorieID IS NOT NULL BEGIN DECLARE @LicentieVerplicht int SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID) END IF (@ShowAllJN = 0) BEGIN SET RowCount 100 END CASE @LicentieVerplicht WHEN '1' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND vwClubLidPersoonAlgemeen.LicentieJN = 1 Order By Persoon END WHEN '2' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) ORDER BY Persoon END WHEN '3' THEN BEGIN SELECTDISTINCT ClubStamnummer, ClubID, ClubNaam, LidID, PersoonID, Persoon, LidFederatieNummer, LidClubNummer, vwClubLidPersoonAlgemeen.LidClubID, vwClubLidPersoonAlgemeen.LicentieJN, UserName, PersoonActiefJN, LI.SeizoenID, LI.LidFederatieJN FROM vwClubLidPersoonAlgemeen INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC) WHERE LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND Persoon LIKE '%'+@PersoonNaam+'%'AND ClubStamnummer LIKE @ClubStamnummer AND ClubNaam LIKE '%' + @ClubNaam + '%' AND (@ClubID = 0 OR ClubID = @ClubID) AND (@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND (@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND vwClubLidPersoonAlgemeen.LicentieJN = 0 Order By Persoon END According the value of my @LicentieVerplicht variable: DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID) My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement
View Replies !
Case When In A Select Statement
Hi everyone, I have the following problem withj this query: SELECT DocNumber, Title,FirstName,LastName,Paper, ANNO,Location,Keywords, URL= case WHEN URL IS NULL THEN '|<a href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+ CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+ '&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' + CONVERT(varchar,Firstname)+ '&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')> Order through ILLiad</a>' ELSE URL END FROM DocTable1 WHERE FREETEXT.... When URL is null, I don't get the string showing. It works properly only in the query analyzer. Any ideas? Christian
View Replies !
Select Within Case Statement?
Limitations: Needs to be single sql statement for exec, no udf/stored proc Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice! tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to tblAffiliation: affiliation_pk, organization_fk tblOrg: organization_pk, city_fk tblCity: city_pk, county_fk tblCounty: county_pk, s_fk For resulting display only: tblPersonArea: personArea_pk, area_fk, person_fk tblArea: area_pk, areaDesc, division_fk tblDivision: division_pk, divisionDesc
View Replies !
Case Statement Within A Select
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started. Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger: Code: insert into @temp_par select CONVERT(varchar(20),d.PAT_ID), ep.EXT_PAT_ID, d.DRUG_DESC_ID, d.ALLERGY_CD, CONVERT(varchar(20),d.PAR_DT,120), ISNULL(d.PAR_TXT, 'NULL'), ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'), ISNULL(m.DOSAGE_FORM_CD,'NULL'), ISNULL(m.STRGTH,'NULL'), ISNULL(m.STRGTH_UM,'NULL') from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example: Code: 1 = A 2 = B 3 = C 4 = D 5 = E Any help?
View Replies !
Select Case Statement
INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus) SELECT s.Hic, s.DtImported, s.Source, dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID, m.LastName, m.FirstName, dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID, s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New' CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old ElSE 'No Valid' End from tbEsccSuspects s inner join tbPDMMembers m on s.Hic = m.HicNumber Where s.EUFStatus = 1 -- AND DtEUFSent is NULL what I am doing wrong in the case statment that I am getting an error. ?????
View Replies !
Select Case Statement
Hi, I'm wondering what syntax to use for a select case statement. I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions. For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions. So- I'm saying: select case when question_id = 488 then don't return question_id 220, 962, 963 I'm trying something like this: select q.client_id, case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id from questions_yes_no q How would I say 'dont show' question 220? End is not the way to go, obviously. If you can see what I'm trying to do, can you point me to an article or bol search? Thank you for any help.
View Replies !
Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement??? is this correct ?? its showing me errors Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ??? (case when 'DataSource =1' then 'ABC' then 'BCD' endcase) Where ..... Pls correct me Thanks
View Replies !
Using A CASE Statement Within A Select Query
Hi folks, Hope you are all well. I am using a CASE statement within a SELECT query to sum up values for different customers. SELECT CR_CUST.Customer_Code, 'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END FROM CR_CUST INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code WHERE (CR_PROD.Part_Class_Code = 1 OR CR_PROD.Part_Class_Code = 7 OR CR_PROD.Part_Class_Code = 8) GROUP BY CR_CUST.Customer_Code, CR_PROD.Part_Class_Code The above query produces the following results... Customer_Code General_01 General_07 General_08 ------------- ---------------- ---------------- ---------------- 02210 10074.30 .00 .00 02347 7606.49 .00 .00 02210 .00 12618.42 .00 02347 .00 13131.63 .00 02210 .00 .00 4505.44 02347 .00 .00 5018.03 My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so... Customer_Code General_01 General_07 General_08 ------------- ---------------- ---------------- ---------------- 02210 10074.30 12618.42 4505.44 02347 7606.49 13131.63 5018.03 I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table. SELECT Customer_Code, SUM(General_01), SUM(General_07), SUM(General_08) FROM #MyTempTable GROUP BY Customer_Code Thanks in advance, Kev
View Replies !
Using Case In A Select Statement,urgent!
Hi All, I need a hand in this script. SELECT a.id ,value = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END ,value2 = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END FROM table_name a where value > 0 ORDER BY VALUE The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help! Thank you in advance!
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Case Does Not Work In My Select Statement
Hello everybody I have problem with CASE statement. Here is select it Code Block select mev.Id ,mev.MetaElementId ,mev.ElementValue ,mev.DocumentId ,me.ElementTypeId ,castedValue = case when me.ElementTypeId =3 then cast(mev.ElementValue as integer) when me.ElementTypeId =4 then cast(mev.ElementValue as datetime) end from dbo.tbMetaElementValue mev inner join dbo.tbMetaElement me on mev.MetaElementId = me.Id where mev.MetaElementId =7 it returns Id MetaElementId ElementValue DocumentId ElementTypeId castedValue 49 7 2006 28 3 6/30/1905 0:00 53 7 2004 30 3 6/28/1905 0:00 61 7 2006 36 3 6/30/1905 0:00 67 7 2005 38 3 6/29/1905 0:00 70 7 2004 39 3 6/28/1905 0:00 105 7 2003 63 3 6/27/1905 0:00 166 7 2006 109 3 6/30/1905 0:00 195 7 2005 129 3 6/29/1905 0:00 220 7 2005 150 3 6/29/1905 0:00 223 7 2006 151 3 6/30/1905 0:00 As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line Code Block when me.ElementTypeId =4 then cast(mev.ElementValue as datetime) it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting Looks like I am missing something really fundamental. Any help is apreciated ! Thanks
View Replies !
First Time SELECT CASE Statement User
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
View Replies !
Case Statement Within A Select Where 2 Or More Instances Of The Record Exist.
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
View Replies !
Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far: SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated. Thanks!
View Replies !
Case Sensitive?
Dear everyone, I am doing Login webform (C# .NET web application) with SQL Server 2000. The staff table is to store authenticated user info. But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login. Could anyone tell me how to solve this problem?? Thanks you very much!! private void btnLogin_Click(object sender, System.EventArgs e) { //instantiate SQL connection SqlConnection sqlConnect = new SqlConnection(connectStg); SqlCommand selectLogin = sqlConnect.CreateCommand(); 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"; } else { lblLoginFail.Visible = true; lblLoginFail.Text = se.Message; } } //close SqlDataReader and SqlConnection loginReader.Close(); sqlConnect.Close();
View Replies !
Case Sensitive
Hi, 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. Regards, Susan
View Replies !
Case Sensitive
In SQL Server 7.0, how do you SELECT a column which has values beginning with only lower case letters can anyone advise?
View Replies !
Case Sensitive
Hi, I am using SQL Server 2000. How to make case sensitive of a database. Eg : Should work Select * from Employees Select * from Employees where Title like 'S%' Should not work select * from employees select * from employees where title like 's%' Thanks in advance.
View Replies !
Case Sensitive SQL - Pls Help A Noob
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?
View Replies !
Case-sensitive Search In Sql 7
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 Replies !
Case Sensitive Vs Insensitive
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 Replies !
SQL Passwords ARE Case-sensitive, Right?
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).
View Replies !
Help With Case Sensitive Data
I am running an update step on a table, where it selects a string pattern from another column in the table. I need to know which command to use to search for only an uppercase string. I'm using the following syntax: update <table> set column_a= (select substring(column_b, charindex(upper('IN'), column_b), 8)) This sql finds the pattern "IN" in column_b and updates column_a with this string "IN" (including the 6 characters following this pattern). How do I run this search where it only finds and returns uppercase "IN" as opposed to lowercase "in". Any suggestions will be greatly appreciated Thanks in advance
View Replies !
Case Sensitive Queries
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? Regards, Gavin Patterson
View Replies !
Case-sensitive Search
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 Replies !
Case-sensitive Search
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 Replies !
PIVOT Is Not Case-sensitive
Hi there, I am using a PIVOT to count the number of chunk for each block type: ex.: block_type, chunk a, <data> a, <data> b, <data> ... My problem is that the block_type is case-sensitive, 'a' should not be counted as a 'A'. How can I take the case in consideration? I've tried to plug a COLLATE SQL_Latin1_General_CP1_CS_AS statement but it doesn't seem to be supported... Something like: SELECT * FROM recv.test_Blocks PIVOT ( COUNT(chunk) FOR block_type COLLATE SQL_Latin1_General_CP1_CS_AS IN ([9.], a, B, h, q) ) AS pvt Also something like: IN (a, A) returns an error: The column 'A' was specified multiple times for 'pvt'. Thanks
View Replies !
Case - Sensitive Field
I want to specify the data in columns are both upper & lower case(i.e. one column data having all lowercase data or uppercase data ). Ex:- if i have one column - col1 and its 1st value is RAKESH JHA 2nd value is rakesh jha then how i can find that, how much row are in upper case. respectively lower case
View Replies !
How To Do Case-sensitive Search
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 Replies !
Case-sensitive SQL Server
Dear SQL Experts, Can I change an existing 2000 sql server to case-sensative? Books on-line tell you how to create one during setup, but requirements around here dictate that I chage my existing setup. Any help would be greatly appreciated. Thanks
View Replies !
Case-sensitive Security
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? Thnx! Phil
View Replies !
|
TRACKER
