We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Nulll Query

Medium Priority
225 Views
Last Modified: 2013-11-29
please find below my coding
trim(iif(isnull[UserAddress1])=true,"",[UserAddress2]))
i don't want use Double Quatation mark. ("").becuase when my manager searching not null records using IS NOT NULL keyword its not showing any records. i have to use for that <>"", He don't like it.becuase he is thinking some hidden data in the records.
So, Can any body help me to solve this above issue.thx
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Use this:

Trim(IIF(UserAddress1 IS NULL, "", UserAddress2))

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You basically have to put back the NULL's :)
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
NULLs should stay NULL.
Empty strings ("") will now become NULL.
Other text will remain same unless had extra spaces, it will be trimmed.

Author

Commented:
its possible to use like this
trim(iif(isnull[UserAddress1])=true,NULL,[UserAddress2]))

I DON"T WANT TO USE "" DOUBLE QUATATION MARK.
thx
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Since you DON'T want the double quotation marks, you will have to check for that and eliminate it.  There is no point in checking for NULL just to replace with NULL.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Oh wait, I see now UserAddress1 is a different field than UserAddress2.  Thought they were the same.  That should be fine, although do you know for sure you don't already have "" in some of the UserAddress1 data.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
If you do then...
(what this does is makes NULLs, "", and " " all equal NULL)
IIF(TRIM(NZ([UserAddress1], ""))="", NULL, TRIM([UserAddress2]))

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.