Return a case sensitive record

Asked

Viewed 75 times

4

I own a constraint in the bank that is all with your name capitalized. As I do a count to find the same one using the UPPER? 'Cause I want to pass the where a tiny name to return. This one of mine query is not working.

select count(*)
 from all_cons_columns
 where UPPER(constraint_name) = 'FK_od8ou6g1l9t7iufcp3o0to189';

That is, returns 0 when it should return 1. If I put the name as it is in the database returns 1.

sql string oracle

2 answers

5

If you want to compare all uppercase then compare with the other side all uppercase too:

select count(*)
 from all_cons_columns
 where UPPER(constraint_name) = 'FK_OD8OU6G1L9T7IUFCP3O0TO189';

Actually the ideal would be to use a COLLATE that does not consider the `Casing. Behold article on the subject.

If it were a variable like a meter, then it would be:

select count(*)
 from all_cons_columns
 where UPPER(constraint_name) = UPPER(parametro);

If it is guaranteed that the column will always be capitalized it would be:

select count(*)
 from all_cons_columns
 where constraint_name = UPPER(parametro);

I put in the Github for future reference.

Anything that runs away from this makes no sense. Anyway it’s a palliative, use the collation is the right thing to do.

  • That doesn’t suit me because I’ll always get the name in lowercase.

  • You will receive where??

  • My situation is as follows: Imagine that in the bank the Constraint already exists. They ask me to check if it already exists. When they ask me, they tell me her name with the lower-case name. I would have to convert it to upper-case and then query it ? No, I want the select to return if it exists independently as I put in the Where.

  • I don’t understand what you want, I don’t know what constraint It has to do with that, I don’t know where this information you’re saying comes from, I don’t know why you’re talking in lowercase now when the question is uppercase, or what the difficulty is. If you’re not gonna wear one collating then you have to make both sides uppercase or all minuscule, it doesn’t matter.

  • I found the solution, I will post and maybe it is clearer what was my doubt.

0

The solution was to pass the UPPER in the name of the Constraint:

select count(*)
 from all_cons_columns
 where constraint_name = UPPER('FK_od8ou6g1l9t7iufcp3o0to189');

  • This doesn’t make any sense, if it’s all uppercase, you don’t have to use a UPPER().

  • I edited the answer, now yes it makes sense. I pass in Where the name in lowercase.

  • It still doesn’t make sense because all you have to do is write in capital letters, you don’t need the function.

Browser other questions tagged sql string oracle

You are not signed in. Login or sign up in order to post.