-1

I have a varchar column, set as varchar(255), but i cannot query using an = operator.

I know there is data in this set where the field (UOM) = 'PK', but when i query this i get no results. If i query UOM LIKE '%PK%', i get results, but not using a straight equal operator. I have tried changing the datatype to nvarchar, and also tried seeing if there were spaces in the columns throwing it off, but no luck.

Has anyone run into anything like this, and how did you solve? Could the column be corrupted?

underscore_d
  • 6,309
  • 3
  • 38
  • 64
slevin37
  • 33
  • 1
  • 2
  • 9
  • 1
    If the `LIKE` works, then the column contains more than just `PK`, what exactly are you trying to figure out here? – DavidG Apr 19 '18 at 13:50
  • What do you get if you do `LEN(UOM)`? This will give you an idea of how many extra characters you are looking for. – SQLChao Apr 19 '18 at 13:55
  • Try converting the value to ASCII codes, that will show you exactly the content, for example: https://stackoverflow.com/questions/4492205/convert-varchar-to-ascii – DavidG Apr 19 '18 at 13:58
  • Thanks David! If you see my answer posted below, thats exactly what i did! – slevin37 Apr 19 '18 at 14:02
  • 1
    Note that when someone provides you a possible answer, it's good etiquette on this site to let them post the answer, something to remember for next time :) – DavidG Apr 19 '18 at 14:05
  • Theres no need to downvote me over that? I posted it before you posted this comment. Sorry – slevin37 Apr 19 '18 at 14:11
  • 1
    I didn't downvote you I'm afraid, that was somebody else. – DavidG Apr 19 '18 at 14:12

2 Answers2

0

Thanks for the replies all! I found this helpful article which showed some hidden ASCII characters in the field. A quick replace statement and we're back to functioning! Thanks again everyone for the quick replies.

slevin37
  • 33
  • 1
  • 2
  • 9
-1

Most likely a leading space... try where ltrim(UOM) = 'PK'. Most often, trailing spaces do not affect equality operations, but you could also do where ltrim(rtrim(UOM)) = 'PK'. I wouldn't expect there to be a case sensitivity issue, but keep that in mind when comparing strings as well, and this is where you may want to use the UPPER() or LOWER() methods.

Next, you'll want to start looking for carriage returns, line feeds, tabs, etc.

declare @var varchar(64) =  char(10) + --LF
                            char(13) + --CR
                            'PK'
select 
    case when replace(replace(@var,char(10),''),char(13),'') = 'PK' then 1 else 0 end
    ,case when @var = 'PK' then 1 else 0 end

Naturally, you could just clean your data if this is the case, or continue to us LIKE

S3S
  • 24,809
  • 5
  • 26
  • 45
  • Thanks for the idea! I looked at trailing spaces, but didnt think of leading spaces. However, I did try this just now and still no luck. – slevin37 Apr 19 '18 at 13:50
  • 2
    Could be all sorts, trailing crlf, other hidden chars etc. – DavidG Apr 19 '18 at 13:50
  • Very uncommon @DavidG but yes, this could be the case. I'll update – S3S Apr 19 '18 at 13:51
  • 2
    Why is it uncommon? Happens all the time, especially when importing data from external sources (I'm talking about you Excel) – DavidG Apr 19 '18 at 13:52
  • HAHA, hardly worth a DV but importing data into RDMS isn't the most common practice. While it happens, I'd argue it is far less common than OLTP from an application. And most developer would *cleanse* this before importing to avoid this... – S3S Apr 19 '18 at 13:56
  • Wasn't my DV but to claim importing data into a database isn't common is kinda absurd. Your update is better, but still leaves other random characters that may be confusing the issue. Better to have a generic solution. – DavidG Apr 19 '18 at 13:57
  • You'd argue that more user RDMS import data from Excel, flat files, etc more often than they are populated via transactions from an application? I'm not saying it's a unicorn, I'm saying that i'd think the majority of the instances out there aren't doing this often. – S3S Apr 19 '18 at 13:58
  • There's a huge difference between saying "importing data into RDBMS is uncommon" and then saying it's "less common than OLTP". Of course, doesn't mean it doesn't happen all the time. Your answer makes an assumption that the rogue values are from a very small set where they could be literally thousands. – DavidG Apr 19 '18 at 14:01
  • True @DavidG there could be hundreds. That’s why I said carriage returns, line feeds, **etc** and was trying to show an example of how to replace these. I’d use a tally table with a cte to account for all occurrences . – S3S Apr 21 '18 at 20:57