March 23, 2019 at 2:37 pm
I have this table and I'm writing VB.Net code to find the error based on supplied TAG/s, here is my initial T-SQL query which i know is horrible I'm looking for some help on rewriting this so it actually works.
Thanks
Madaxe
SELECT
ERROR_MESSAGE
FROM
LEKO.UI_ERRORS
WHERE
(UPPER(TAG1) LIKE UPPER('%New%') OR TAG1 is null)
AND (UPPER(TAG2) LIKE UPPER('%Passwords%') OR TAG2 is null)
AND (UPPER(TAG3) LIKE UPPER('%Dont%') OR TAG3 is null)
AND (UPPER(TAG4) LIKE UPPER('%Match%') OR TAG4 is null)
AND (UPPER(TAG5) LIKE UPPER('') OR TAG5 is null)
ID OBJECT_ID TAG1 TAG2 TAG3 TAG4 TAG5 ERROR_MESSAGE FK_OBJECT_ID_MOD_USER MOD_DATE
2 89 User Login Field Empty Not all Login Fields were Populated. 1 21-MAR-19
3 91 Password Failed Reset Password Failed to Reset. 1 23-MAR-19
4 92 Password Reset Successful Password Reset Successful. 1 23-MAR-19
5 93 New Password Must Differant New Password Must be Differant. 1 23-MAR-19
6 94 New Password Dont Differant New Passwords Dont Match. 1 23-MAR-19
7 95 You Must Logged Reset Password You Must be Logged in to Reset Password. 1 23-MAR-19
8 96 One New Password Fields Empty One of the New Password Fields Were Empty. 1 23-MAR-19
March 23, 2019 at 2:44 pm
Why not set the collation to case-insensitive instead of doing all those converting to upper case? But without some sample data, and a sample of query parameter values, it's hard to say.
March 24, 2019 at 8:31 am
pietlinden - Saturday, March 23, 2019 2:44 PMWhy not set the collation to case-insensitive instead of doing all those converting to upper case? But without some sample data, and a sample of query parameter values, it's hard to say.
Changing the collation in a WHERE to a case-insensitive one won't help with performance here. If the OP wants to do case insensitive searches they should either add a persisted computed column with the value in a different (case insensitive) collation, or with the the entire value in upper case.
The OP has actually supplied some sample data here, however, it it completely unreadable. Looking at the data, there doesn't appear to be enough columns in some of the rows; making it useless. Perhaps, ma_jeeves, you could instead post it as DDL and DML; or otherwise format the data so that it is properly aligned (and include your NULL values, as your query implies you have them).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 25, 2019 at 11:42 am
Also, there is no purpose to writing UPPER(<some constant string> ), especially if the string doesn't contain alphabetic characters.
UPPER('%New%') is the same as '%NEW%'
UPPER('%Passwords%') is the same as '%PASSWORDS%'
UPPER('%Dont%') is the same as '%DONT%'
UPPER('%Match%') is the same as '%MATCH%'
and UPPER('') is the same as LOWER('') is the same as ''.
Drew
Edited to remove the auto-smiley.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply