Select not empty on data type=text fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select not empty on data type=text fields

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2007-03-09 : 14:55:22

Hi,

How do you create a select statement based on if a field that is of data type 'Text' is empty or not. I have a table with a text field that holds large amounts of text. I need pull only the records where the text data type field is NOT empty.

Select * from Table1 where Mytextfld IS NOT EMPTY
Select * from Table1 where Mytextfld != ''
Select * from Table1 where LEN(Mytextfld)>'0'

Please help.

Thanks,

Razzle

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 15:04:16

[code]Select * from Table1 where Mytextfld IS NOT NULL[/code]

Also, when you use numbers in SQL Server, don't enclose them in quotes, and use the datalength function for the text type, so one of your other queries should be

[code]Select * from Table1 where datalength(Mytextfld) > 0[/code]

Razzle00
Starting Member

35 Posts

Posted - 2007-03-12 : 11:17:49

Thanks snSQL,

datalength(Mytextfld) > 0, is working for my query. One other question though. If the I have field which data type is text, and it does not allow nulls and the value is an empty string like '', is there a function in SQL like Is Null that work for string empty? In VB.net I can check a string variable like this...

Dim MyString as String = ""
If MyString.equals(string.empty)
msgbox("String is Empty")
else
msgbox("String is NOT Empty")
End If

Razzle

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 11:53:53

Well that would be datalength(MyString) = 0 right?

Razzle00
Starting Member

35 Posts

Posted - 2007-03-13 : 13:44:05

LOL, I guess that would be the solution. So basically the datalength() function is the best way to handle empty/not empty strings in SQL server. I just thought since you could use IS NULL/IS NOT NULL for null values there might be a function for empty/not empty for string values.

Thanks for the help,

Razzle

Razzle00
Starting Member

35 Posts

Posted - 2007-03-13 : 13:51:52

Just to further explain the madness to my question. I come from a Visual FoxPro background where it was a standard practice to evaluate empty strings in a database using an Empty() function.

Example
The following example opens the customer table in the testdata database. FOR ... ENDFOR is used to create a loop in which EMPTY( ) s used to determine if TAG( ) returns the empty string. The name of each structural index tag is displayed with its candidate status
.
Example
The following example opens the customer table in the testdata database. FOR ... ENDFOR is used to create a loop in which EMPTY( ) s used to determine if TAG( ) returns the empty string. The name of each structural index tag is displayed with its candidate status
.


CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
USE customer     && Open customer table

FOR nCount = 1 TO 254
   IF !EMPTY(TAG(nCount))  && Checks for empty string
   ? TAG(nCount)  && Display tag name
   ? CANDIDATE(nCount)  && Display candidate status
   ELSE
      EXIT  && Exit the loop when no more tags are found
   ENDIF
ENDFOR

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-13 : 13:56:07

Correct, there isn't a specific keyword, you could of course also just use an empty string with two single quotes and nothing between them.

Select * from Table1 where Mytextfld <> ''

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources