LTRIM,RTRIM

Question

LTRIM,RTRIM

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

kk1173 (4/2/2013)
If I have a query like
Select * from table1 WHERE ColVal IN (Select AllVals from table2)
Is it not better to have the following instead? It will bring in those values that have trailing and leading spaces also.
Select * from table1 WHERE LTRIM(RTRIM(ColVal))
IN (Select LTRIM(RTRIM(AllVals)) from table2)

The query you posted second is not sargable. What that means is that if there is an index on ColVal it will be ignored and the engine will perform a scan instead of a seek.

From Jeff's post on page 1:

SARG stands for "Search ARGument" and "SARGable" has come to mean that the search arguments in a WHERE clause, ORDER BY, and/or ON clause are capable of doing an INDEX SEEK if the appropriate index is available and used.
Search arguments that modify a column will only allow for either a table scan (includes Clustered Index Scan) or a non-Clustered Index Scan.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kk1173 Ten Centuries Points: 1000 More actions · Answer 2

So both the queries will return same results? I want to include leading and trailing spaces from table2.

If I understanding correctly, LTRIM/RTRIM does not make any difference.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

kk1173 (4/2/2013)
So both the queries will return same results? I want to include leading and trailing spaces from table2.
If I understanding correctly, LTRIM/RTRIM does not make any difference.

If you want to include the leading and trailing spaces why are using either of the trim functions at all?

Those two queries will NOT necessarily return the same thing. When comparing string values trailing spaces will be ignored but leading spaces will not. In your case you might have ' this value ' in one table but 'this value ' in the other table. The only way those two values will be the same is if you LTRIM the values. Does that help to clarify?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

kk1173 (4/2/2013)
Is it not better to have the following instead? It will bring in those values that have trailing and leading spaces also.

So you have dirty data and would rather make queries perform badly than fix the data?

Unless leading spaces are significant and meaningful, data properly cleaned and inserted should not have leading spaces. Trailing spaces will be ignored, so there's no need for RTRIM in your example. You only need LTRIM if you know you have bad data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kk1173 Ten Centuries Points: 1000 More actions · Answer 5

Sorry, I was not clear. I want include values from table column that has leading/trailing spaces also.

For e.g.

table1

Col

----------

' ABC'

'XYZ '

' MNR'

' MCR'

table2

Col

-------

'XYZ'

'MNR'

Select * from table1 where Col IN (Select Col from table2)

I want to return XYZ and MNR values as a result of this query.

Looks like i do not need to do any trimming even though table1 have spaces

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Why do you have data with leading spaces in the tables? Why have you not cleaned that data and removed the leading spaces? (and yes, you will need an LTRIM and hence a poorly performing query to do what you want)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kk1173 Ten Centuries Points: 1000 More actions · Answer 7

You are correct, the data should be clean. But sometimes we have no control on the data and it is provided by third party. All we can do is some defensive programming.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

The third party inserted it into your database and you don't have rights to run updates?

In this case, defensive programming = slow queries. If your users are happy to have things running slower than they could, then fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kk1173 Ten Centuries Points: 1000 More actions · Answer 9

Its not that simple.

The third party provides data, another team loads the data and we use the data to display on front end.

The team that loads the data will need to clean the data, but they do not want to do it and we do not have rights to update the data.

mahavidhya24 SSC Veteran Points: 204 More actions · Answer 10

L TRIM removes all the white space from the starting of the pattern and R TRIM removes all the white space at the end of the pattern.:-)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 11

mahavidhya24 (8/26/2013)
L TRIM removes all the white space from the starting of the pattern and R TRIM removes all the white space at the end of the pattern.:-)

Sorry, but that's factually incorrect for SQL Server. For SQL, LTRIM and RTRIM affect only spaces, not all whitespace characters.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

kk1173 (4/2/2013)
Its not that simple.
The team that loads the data will need to clean the data, but they do not want to do it and we do not have rights to update the data.

Wow. I really feel for you there. I have to stop there because nothing civil would come out of my mouth about such a "team".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)