0

I need to find data with a list of values using the IN operator but one lookup value in my list contains single quote:

SELECT * FROM Table WHERE Errorcode IN ( 'subsriber's phone number is blank.', 'Primary address cannot be PO Box.', 'Address is invalid.', 'City/ST/Zip Code mismatch.' )

Can I use a SUBSTRING function to truncate the first lookup value? It HAS to be included in order to pull the correct number of rows. The ErrorCode column is a VARCHAR (max) datatype and null is permitted.

ANas200
  • 1
  • You want to truncate the input so that one of the values isn't even searched for correctly? What if two strings have a single quote? What if they all do? Wouldn't you rather escape the characters so that you can still pass in the data and get valid results, instead of pretending some of the data doesn't exist? – Aaron Bertrand Jan 10 '22 at 22:15
  • Thanks for your input Aaron! I don't want to truncate it so its not searched for. I want to know if truncating one or two items from my list was an option to begin with. I actually have two issues: one being that some of the values contain single quotes in the lookup values, the other being some of the values have a phone number at the end of each lookup value that need to be truncated (I only need to capture the first x characters of that list value. ) – ANas200 Jan 11 '22 at 15:00
  • The single quote issue is a simple problem - you replace any single quote (`'`) with two (`''`) or, better yet, you pass the value in as a proper parameter, and you don't have to worry about. The phone number partial match is an entirely different problem and deserves its own question (but please search first). – Aaron Bertrand Jan 11 '22 at 15:03
  • Will do, thanks again! – ANas200 Jan 11 '22 at 18:10

0 Answers0