September 20, 2007 at 1:51 pm
We have a customer name with quotes in it - ABC (After Breakfast Corp.)
This didn't work:
WHERE (cust_name <> 'ABC (After Breakfast Corp.)')
I figure the parentheses are throwing it off, but I can't find anything in manuals or online that addresses this specifically.
September 20, 2007 at 2:11 pm
Parentheses will not be your issue. You also say you have quotes in the name but I don't see that?
Try look for = value and try like 'ABC%' to make sure the data you expect is there.
September 20, 2007 at 5:25 pm
No, there are no quotes within the name; the name is surrounded by quotes. When I changed the code to WHERE (SUBSTRING(cust_name, 1, 3) <> 'ABC')), that worked, but I need to use the full customer name, as above.
September 20, 2007 at 11:28 pm
Do you mean that the actual data in the cust_name field has quotes around it, ie 'ABC (After Breakfast Corp.)'? If the quotes are actually in the data itself then you need to double each single quote that exists within the data and then enclose the whole field value in a set of single quotes.
If this is the case, try this as your where clause:
WHERE (cust_name <> '''ABC (After Breakfast Corp.)''')
September 21, 2007 at 3:49 am
Did you copy and paste the name of company into your query?
Remember that your query looks for the precise value, which can be different from what you see. If you wrote it, you may have some "invisible" characters missing. For example, there could be a space at the end of the name, or 2 spaces instead of one somewhere in the middle, or there is some other character that does not show up at all.
Try to compare length of the two values:
SELECT LEN('ABC (After Breakfast Corp.)')
SELECT LEN(cust_name)
FROM yourtable
WHERE .... = ....(supply the column name and value that uniquely identifies the row with ABC (After Breakfast Corp.) - e.g. primary key)
September 21, 2007 at 6:31 am
Also check if the field is defined as CHAR. If it is right-filled with spaces you won't be matchiing your pasted text.
September 21, 2007 at 11:34 am
check your ascii values. I have seen where spaces and dashes etc were saved with a different ascii value of what you enter to compare with. Though the values visually they look the same they are not.
Replace the values in the single quotes
declare
@i int
set
@i = 1
while
@i <= len('Pasted From DB')
begin
select
ascii(substring('Pasted From DB',@i,1)), substring('Pasted From DB',@i,1),
ascii(substring('What you are entering',@i,1)), substring('What you are entering',@i,1)
set @i = @i + 1
end
September 22, 2007 at 9:05 am
Problem solved. Thanks so much!
September 24, 2007 at 2:47 am
Could you tell us how it was solved? It may help other people later when they have the same or similar problem... and, of course, we are curious, too 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply