I am storing member file references in a database using UUIDs as the record id and am able to retrieve records by searching for an id that matches the passed UUID. However, I have a situation where I am passing the UUID into my query and it is not finding the record. If I find all records based upon the member number, I am able to see the record, but if I search for this one id, the record is not found. Any ideas as to why this is happening? Here is the query and it is running on SQL Server:

SELECT * FROM dbo.memberFile WHERE id = '961E4BFF-0B9D-AD1D-492BD68DF5093086'

Also, this has been working for years, it is just this one particular UUID that I am having an issue with.

what’s the datatype of the column in the database? how do those UUIDs get inserted?

It is a char(35) datatype. I am using createUUID to generate the code and using a standard INSERT query to create the record.

is the database and/or table’s collation case-sensitive?

I am not 100% positive, as I am not logged into the server at them moment and my SQL client doesn’t show collation. Though, I am literally copying the id number from the table itself, writing the query that I shared and the database is not returning the record. If I copy one of the member’s other file’s id and run same query, that record is returned properly.

That makes me think there may be a character-set or collation difference or something like that getting you. It’s the only time I’ve seen something like that (although that sort of begs the question of how it got inserted in a manner that might allow something like that to occur, i know). is that column a primary key and/or does it have some sort of uniqueness constraint on it? i might try a series of queries with “like” clauses to see if you can tell where the record’s ID stops matching. Something along the lines of

… where like '961E%

then

… where like '961E4BFF%

, etc?

The column is designated as the primary key, so it does require a unique value, but other than that, there aren’t other constraints applied to it. To answer your question, I am not sure if this is indeed the only record that has an issue, it is just the first time that I’ve run into it. I’ll need to run some more tests on other file ids. I was thinking that it may be some character combination that is maybe being interpreted as an escape character or something, which would be extremely odd. I think that your suggestion of using the LIKE operator to try to narrow down where the file id runs into a problem.

I agree, it feels like a stretch but it’s the only thing that sort of makes sense. there’s clearly something the database thinks is different between what you’re asking for and what it has. the char(35) aspect eliminates invisible/nonprintable characters somehow having gotten in there, which is good.

Thanks for your help.

i wish i had something more definitive to offer. 🤷

as an experiment i would try something like this....

DECLARE @StringToSearch as char(35) = '961E4BFF-0B9D-AD1D-492BD68DF5093086'
DECLARE @RowID as char(35) 
SELECT @RowID = ID FROM dbo.memberFile where ID like concat(@stringToSearch, '%')

And then I would play with the data to see where it stops being a match ... print out both if it doesn't fin dit then start reducing how many characters are in you @stringToSearch...

you sure it's 961E4BFF-0B9D-AD1D-492BD68DF5093086 and not 961E4BFF-0B9D-AD1D-492B-D68DF5093086? note the introduction of the fourth hyphen

^^^ wouldn't fit in a

char(35)

. Are you passing the value as a param, or hard-coding it in the SQL statement? If the latter, any difference if you do it using a parameter instead?

Any chance there's leading or trailing whitespace in either the record value or the search value?

Have you tried a “trim” of the ID, or doing a “LIKE” instead of “=“?

@Dean Lawrence I would try rebuilding your indices on the table. It sounds like the index being used for your query might be missing this particular value, which is why it's not showing up.

@Dean Lawrence Mostly because I’m curious… did you figure anything out on this?

@rstewart Sorry, for the delayed response. Unfortunately, no. I ended up manually deleting the record via SSMS and having the member re-upload the file. The new UUID that was generated works fine. 🤷‍♂️