If trim is used in Select, does it have to be used in Where?-CodePudding

This should be an easy question.

If the trim function has been used on an ID in a Select statement, does it have to be used on the ID in a Where clause? Or can the trim function be left out in the Where clause?

SELECT (TRIM(a.T$ID)) as "ID" 
FROM SCHEMA.DDiitm0011 a
WHERE TRIM(a.T$ID) LIKE '4U%'

CodePudding user response：

If the trim function has been used on an ID in a Select statement, does it have to be used on the ID in a Where clause?

There is no general requirement for the function to be applied in both places. It depends on the data and the logic you need to apply to your exclusion filter, and - separately - how you want to return the matching values. You won't get a syntax error if you trim in the select list and not the where clause, or vice versa; but you might not get the result you want if you use the wrong expression(s).

I actually want to exclude ID's that start with 4U. Would this WHERE clause suffice? WHERE (a.T$ID) NOT LIKE '4U%'

Yes, though you don't need the parentheses either:

SELECT (TRIM(a.T$ID)) as "ID" 
FROM SCHEMA.DDiitm0011 a
WHERE a.T$ID LIKE '4U%'

That will exclude values starting with 4U, such as '4U', '4U ', '4UP', '4UNDER ' etc.

It will not exclude any that have spaces before that, such as ' 4U' or ' 4UP'.

If you wanted to exclude those as well then you could use TRIM(a.T$ID) or LTRIM(a.T$ID) (to only remove leading spaces, not trailing ones - which are covered by the wildcard % anyway). Or you could use a regular expression, but those tend to be significantly more expensive. Either way, applying a function to the column value would prevent a simple index on that column from being used, if it otherwise would be, but you could add a function-based index if that was an issue.

CodePudding user response：

You can use TRIM in WHERE clause like that for example :

DECLARE @ExampleVarTable TABLE(ID INT IDENTITY, Name1 VARCHAR(100))
INSERT INTO @ExampleVarTable (Name1)
VALUES('  Toto '), ('Toto'), ('Titi'), (' Titi   '), ('  Toto')

SELECT ID, TRIM(Name1) 
FROM @ExampleVarTable
WHERE TRIM(Name1) LIKE 'Toto'

Result :

ID  Name1
1   Toto
2   Toto
5   Toto

But you should make this kind of request :

SELECT ID, TRIM(Name1) 
FROM @ExampleVarTable
WHERE ID = 3

Result :

ID  Name1
3   Titi