Search code examples
sqldb2mainframe

Column shows non-printable values, how do I see what they are?

I have a row in my table where I cannot understand the content. The field is a CHAR (8) and the data should be obviously alphanumeric, but in the result of the select below several "dots" appear according to the first line below:

SELECT DISTINCT (USERID)
FROM USER;
---------------
USERID
---------------
........
AC25
A25A
A25X

It turns out that I cannot get this line in a specific select, it does not return a result in the query below:

SELECT USERID
FROM USER
WHERE USERID = '........';
---------------
USERID
---------------

Can you tell me if there is any clause where you select this WEIRD given from this line?

I've tried low-value but DB2 does not accept, I've never seen this in a table. Can you help me find options for this case other than putting together a query with "not in"?

Solution

  • The dots likely represent non-displayable characters.

    try looking at the hex values

    select distinct
  userid, hex(userid)
from user

    You could then use hex literal to select just that row...

    select *
from user
where userid = x'C3C8C1D9D3C5E2'