• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4486
  • Last Modified:

SQL Select where value1 or value2 are NOT empty

Hi guys,

I have a SQL table and I am I trying to select the below fields where either value1 or value2 are NOT empty.  value1 and value2 will either contain text, or it may contain "                 " as in nothing, or it may be <null>.  I tried using the below to check the datalength but that doesnt seem to work.

Is there a function similar to NOT NULL that instead of null it looks for that blank space?


SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten FROM TABLENAME WHERE datalength(value1) > 0  OR datalength(value2) > 0

Thanks
0
NovoNordisk
Asked:
NovoNordisk
  • 4
  • 3
  • 2
  • +3
1 Solution
 
JMattiasCommented:
Hi,

Add the LTRIM and RTRIM like this datalength(ltrim(rtrim(value1))) > 0  OR datalength(ltrim(rtrim(value2))) > 0

Regards
Mattias
0
 
JMattiasCommented:
Hi,

Add the LTRIM and RTRIM like this datalength(ltrim(rtrim(value1))) > 0  OR datalength(ltrim(rtrim(value2))) > 0
And use IS NOT NULL
(datalength(ltrim(rtrim(value1))) > 0 and value1 is not null) OR (datalength(ltrim(rtrim(value2))) > 0 and value2 is not null)


Regards
Mattias
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
your query looks ok


SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten
FROM TABLENAME
WHERE len(COALESCE(value1,value2))> 0


or

SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten FROM TABLENAME WHERE len(value1) IS nOT NULL  OR len(value2) IS nOT NULL


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NovoNordiskAuthor Commented:
I get the following error when using either aneeshattingal  examples
 
 Argument data type ntext is invalid for argument 1 of len function
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if the datatype is Ntext , use datalength instead of len, i thought it was a varchar column
0
 
NovoNordiskAuthor Commented:
I tried using datalength as you can see by my first post but that didnt seem to work either!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten FROM TABLENAME WHERE Value1 IS NOT NULL OR Value2 IS NOT NULL
0
 
NovoNordiskAuthor Commented:
thats not going to work because there could be an actual real value in the field in which case it wouldnt be a null
0
 
LowfatspreadCommented:
SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten FROM TABLENAME WHERE nullif(Value1,' ') IS NOT NULL OR nullif(Value2,' ') IS NOT NULL
0
 
Anthony PerkinsCommented:
Just another reminder:  The following questions are considered abandoned, please attend to them:
1 01/12/2006 500 SQL and EMAIL  Open Microsoft SQL Server
2 02/01/2006 500 Access denied error when displaying RSS ...  Open Active Server Pages (ASP)
3 02/01/2006 500 Iframe Loading  Open Active Server Pages (ASP)
4 02/07/2006 500 Another Custom VBS Outlook rule  Open Visual Basic
0
 
DDiongsonCommented:
SELECT value1,value2,RequestStatus, CONVERT(SMALLDATETIME, RequestDate, 103) as datetobewritten FROM TABLENAME WHERE (ltrim(Value1)<>'')OR (ltrim(Value2) <> '')

nulls are skipped.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value.
0
 
NovoNordiskAuthor Commented:
Thanks for your help as always guys
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now