OR-statement Returns True CHARINDEX Instead Of Name LIKE
Jun 11, 2013
I have this OR in a simple query:
Code:
DECLARE @searchString nvarchar(100)
SET @searchString = 'sample'
SELECT TOP(1) * FROM user
WHERE (user.identity LIKE @searchString OR
CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0)
PS: Handwritten...
If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).
I've tried various things:
Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P
Flipping the conditions around
Adding/removing paranthesis... without any luck.
Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
Sorry for asking stupid questions...I can't remember which settings in MS SQL define the behaviour of this comparison:select * from table where 'Anna ' = 'Anna'to be TRUE. Both strings are different because the first contains trailing blanks.How to change it to return FALSE what is my expected value?
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).Â
I'm fairly new to SQL, so this might be simple question:
I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query.
Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?...
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar Set @DepartmentName = null Declare @status as bigint Set @status = 4 IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note FROM AdminView WHERE (Status = @status) ORDER BY CallLoggedDT END ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note FROM dbo.AdminView WHERE (Status = @status) AND (DepartmentName = @DepartmentName) ORDER BY CallLoggedDT end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
Hi All, When I use the following I get an error. I think it is because of duplicate records in my table.
Update person Set username = (Select username From update_person Where person.firsname = update_person.firstname and person.lastname = update_person.lastname)
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?
Ok well i have a stored procedure that returns an integer between 1 and 5. My problem now is i want to check to see if the table will return NULL and if so i don't want to assign the value to my variable otherwise it'll trow an error. My code is listed below but i keep getting the error "Conversion from type 'DBNull' to type 'Integer' is not valid." i've also tried If getoptionpicked.Parameters("@optionpicked").Value = Nothing ThenIf getoptionpicked.Parameters("@optionpicked").Value Is system.dbnull Then below is the rest of the code If getoptionpicked.Parameters("@optionpicked").Value Is Nothing Then Else optionpicked = getoptionpicked.Parameters("@optionpicked").Value If optionpicked = 1 Then option1.Checked = True ElseIf optionpicked = 2 Then option2.Checked = True ElseIf optionpicked = 3 Then option3.Checked = True ElseIf optionpicked = 4 Then option4.Checked = True ElseIf optionpicked = 5 Then option5.Checked = True Else End If End If
I am struggling with the below join block in my stored procedure. I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs. I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.
Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves as the primary key.
I am guessing I am encountering this problem because of how I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or do you need more information?
Thanks for any tips.
Code:
select emailAddress from Users union select user_name from PersonalPhotos union select email_address from EditProfile union select email_address from SavedSearches union select distinct email_address from UserPrecedence union select email_address from LastLogin) drv Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress) Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name) Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address) Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address) Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello: I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is: SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997' In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator? Thank you
I want to write a statement something like this SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/) But i want the search to ignore the first 2 digits so that it will return e.g 10/11/1234, 09/11/1234 so that it's only matching the last part Any Help Would be greatly appreciated Thanks
SQL Ver: 2008 (not r2) Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE: DECLARE     @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y')   select @WIPMonthCurrent as WIPMonthCurrent   [code]....
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
SQL Server 2000Ya know, it is always the simplest stuff that gets ya !!I am having the hardest time getting a simple piece of code working.Must be brain dead today.Goal: Get the users full name from a stringHere is sample data:"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"Code:IF LEN(@strReturnValue) > 0 BEGINSELECT @strReturnValue = SUBSTRING(@strReturnValue,(CHARINDEX('CN=',@strReturnValue)+3),(CHARINDEX(',',@strReturnValue)-1))ENDIt will extract:"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"I want it to extract:Kevin JonesThanks.
Hi All,Just wondering if it's any more efficient to use the following SQLstatement to return a partial match from a column as opposed to thesecond statement.SELECT * FROM Table1 WHERE CHARINDEX('value', mycol) > 0Versus:SELECT * FROM Table1 WHERE mycol LIKE '%value%'Does anyone have any thoughts on whether or not either of thesestatements would return a result quicker than the other?Many thanks in advance!Much warmth,Murray
I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.
Here is what the data looks like:
Paper Mill Rd 3332 Allegheny Ave 13 1/2 Balto Nat'l Pike 6600 R-2 Rolling Rd N 3030
Here is the results I am looking for:
street dir street number street name street type street unit 3332 Paper Mill Rd 13 1/2 Allegheny Ave 6600 Balto Nat'l Pike R-2 N 3030 Rolling Rd
Hi, The following stored procedure creates this error, "Syntax error converting the varchar value '17,18,405,408,406,493' to a column of data type int." I tried using a Convert and a Cast function but it still failed. I then read something about using the CharIndex function, but I can't figure it out. Any help would be much appreciated in getting this to work. Thanks
CREATE PROCEDURE [ResourceProducts] @ProductHtml varchar(200) AS
SELECT Resources.ResourceId, Resources.Heading FROM Resources INNER JOIN Products ON Resources.ResourceId IN (Products.Resources) Where Products.ProductHtml = @ProductHtml GROUP BY Resources.Heading, Resources.ResourceId GO
Hi I have a column col1 in a table having below strings. How do I get only the required part of the string as I explaind below. (Needed strings are followed by i.e as below).
1. But the above code only works if the string starts with 'ID='. 2. Because the col1 has strings starts with different letter this code fail to excute and gives error 3. Pathindex with regular express '(patindex('%[^0-9]%',v)-1)' cannot be used because it gets anynumber in the string - string has mixer of numbers and letters
SO IF ANYONE LET ME KNOW HOW TO USE IF or CASE with 'charindex' or any other method to get the above said result would be greatfull.
UserID=176&StoreID=210 sometimes there is more between the two so im tring to use a CHARINDEX and Substring
Substring(Parameters, CHARINDEX('UserID', Parameters),3) When I do this it brings back just the 'USE' I need it to bring back the number associated with it. In this case the 176 how do i get that number?
I have used CHARINDEX successfully to search for a substring going from left to right in a string, but how do I search from the end of a string and go backwards?
Here is a sample of the problem I am trying to solve. Here is a list of cities, some with two names and some with more than two. I need a way to extract only those cities with two names. I wanted to do this by locating the first space in the string and the last space in the string. If the first space equals the last space, then the city should only be a two word city. Otherwise it is a three name city or greater. So what string functions can I use to locate the first and subsequent spaces in a string?
Here is the current version of my SQL statement:
SELECT City.City, CHARINDEX (' ' ,City.City) AS First_Space FROM City WHERE CHARINDEX (' ' ,City.City) <>0
AND HERE IS A SMALL SAMPLE OF WHAT IS RETURNED:
MARINE CITY7 MARINE ON SAINT CROIX7 MARION CENTER7 MARION HEIGHTS7 HIGH RIDGE5 HIGH ROLLS MOUNTAIN PARK5 HIGH SHOALS5
Thanks to all who understand and can offer usable advice in my dilemma.
I have a field containing a string with '/' in it multiple times.How can I return the charindex of the last occurance of '/' in thestring?Regards,Ciarán
What is the best way to essentially use the charindex(find) functionif the value is could be more than one variable (A or B or C)I can't seem to get an "or", "if" or "select if" to workBelow is the code that works, I need 'GOV' to be either 'GOV' or 'FWD'or 'LIB'sqlstring = "SELECT Distinct substring([exposurename]," _& Len(Worksheets(4).Range("j5") & "_VAR_" _&Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 2 _& ",charindex('GOV',[exposurename])-" &Len(Worksheets(4).Range("j5") _& "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0)) + 3 _& ") AS Drivergrp2 " _& "FROM mars.dbo.mroInventoryProductGreeks_Latest" _& "Where producttype = 'creditdefaultswap' " _& "AND exposureName like '" &Worksheets(4).Range("j5") & "_VAR_" &Worksheets(4).Range("C4").Offset(Worksheets(4).Range("c3"), 0) & "%' "_Much appreciation if you can help
Hello,I need to be able to replace only the first occurance of a space characterin a column.Reason being is the data in the column I am trying to replace seems to haveumpteen space characters after each entry, so a simple replace functionwill replace all the spaces after it with what I want!I have thought of RTRIM to get rid of the spaces after and then replace, Ihave also thought of CHARINDEX to find the first occurance of a space andSTUFF to replace it.I have done my homework on these functions!But I am having trouble writing such a statement,I've never written a query which would use more then one function on onecolumn you see and I am getting confused!I'll tell you what I want to do in simple stepsReplace only the first found space in a name column, but then if a name hasa middle initial that will be a problem,Replace that with a dot.then concatanate '@emailaddress;@emailaddress2' after itso when SQLServer does the select it will bring back something likejoe.bloggs@emailaddress;emailaddressBut I guess I'd also need joe.n.bloggs@emailaddress;emailaddressThe data in the column looks like this at the momentjoe bloggsBut I guess there may come a time when we havejoe n bloggs, just to complicate things!What is your advice, and how do I write a query like thisI have been playing around with it in Query Analyser but as I said I amgetting confused and need some help if you don't mindThanks a lot to all who reply :-)RegardsJayne
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
Hello,I was hoping someone could help me with this SQL statement.The column 'options.optionDescrip' is a varchar field.Some values of the 'options.optionDescrip' contain commas, some donot.We are trying to evaluate against any data in the'options.optionDescrip' column located to the left of a comma (if acomma does exist, which it may not). If no comma exists, then we tryand evaluate against the entire field, not just the part to the leftof the comma.SELECT options_optionsGroups.idProduct FROM options_optionsGroupsJOIN Options ON options_optionsGroups.idOption=options.idOptionJOIN products ON options_optionsGroups.idProduct=products.idProduct WHERE (CASE WHEN CharIndex(',',options.optionDescrip) = 0 THENoptions.optionDescrip LIKE '" & gauge & "%' ELSELeft(options.optionDescrip,CharIndex(',',options.o ptionDescrip)) LIKE'" & gauge & "%' END)Thanks for any input you can provide, I appreciate it.
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them. The format of the name field is 'AAAA-BBBBBB-123' here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id. I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID', name AS 'name', sequence AS 'num' FROM FirstTable A INNER JOIN SecondTable q ON (CONVERT(INT,RIGHT(name,3))= a.id) INNER JOIN ThridTable t ON(t.id = q.id) INNER JOIN FourthTable s ON (q.name = s.name ) WHERE A.id = @ID AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1)) ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
I have a column that has 50 records and they are INT. I am trying to place them into a single parameter using the CHARINDEX using the IF Statement. I got an error saying convertinh from varchar to int type data.
Basically, this is what I did: @RoomID varchar(max)
Then I declare it:
DECLARE @RoomId varchar(4)
SET @RoomID = 0
The IF part that I need to express this CHARINDEX function.
Then in Where clause I filtered it as: WHERE r.roomid = @RoomId
Or what other way to handle INT data type and make them available for user to choose multiple value?
