CODEWITHSUNDEEP
sqlsql-servert-sql
Jessica
Jessica

Reputation: 41

LTRIM(RTRIM(COLUMN)) in sql server

I need to create a query to collect all the rows with column A not null and not blank.

When I used this:

AND A.EXP_GRAD_TERM is not null AND A.EXP_GRAD_TERM <> ' '

I got 1169 records which all have some values for the field.

But when I used this:

AND LTRIM(RTRIM(A.EXP_GRAD_TERM)) is not null

I got 1932 records. They have the rows with values and rows with blanks. I even convert the column, the column only has blanks or the values, not other weird invisible character.

I do not know why. I thought they should have worked the same way.

Any thoughts?

Upvotes: 3

Views: 10443

Answers (4)

Hadi
Hadi

Reputation: 37348

LTRIM() function:

Returns a character expression after it removes leading blanks.

RTRIM() function:

Returns a character string after truncating all trailing spaces.

They are different from IsNull or empty string condition (CASE WHEN IS NULL OR <>'')

References

Upvotes: 2

Juanes30
Juanes30

Reputation: 2566

see if you want to get the values that are not null and not empty you can use isnull (exp, replace_value)

AND RTRIM(LTRIM(ISNULL(A.EXP_GRAD_TERM, ''))) <> ''

isnull isnull replaces a null value for which you want to replace it. you could also use COALESCE which "returns the first non-null expression among your arguments"

One apparent advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific. These differences between the two functions are fairly straightforward.

if you want more information about the differences between ISNULL and COALESCE you can see it on this link

Upvotes: 0

forpas
forpas

Reputation: 164139

Combine COALESCE() with LTRIM() and RTRIM():

AND RTRIM(LTRIM(COALESCE(A.EXP_GRAD_TERM, ''))) <> ''

COALESCE(A.EXP_GRAD_TERM, '') will return '' for every null value.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This expression:

 LTRIM(RTRIM(A.EXP_GRAD_TERM)) is not null

is exactly equivalent to:

A.EXP_GRAD_TERM is not null

An empty string is different from NULL, so removing spaces -- even from a string that only has spaces -- has no effect on the NULL comparison.

If you are confused, perhaps you have experience with Oracle where empty strings and NULL are the same thing. That is a peculiar property of Oracle.

Upvotes: 2

Related Questions