Searching a text field

[Ed75]

Sorry if this is a dumb question but I've never had it come up before.

I need to search a text(10) field where the field is not empty. I assume I need to check the field for the first char to no be null but I'm at a loss. Any help would be appreciated. Ed

 

[ptheriault]

Ed,
is this an access database?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[LNBruno]

Something like this?

SELECT FieldName
FROM YourTable
WHERE FieldName IS NOT NULL

< M!ke >
Acupuncture Development: a jab well done.

 

[Ed75]

SQL

 

[Ed75]

SQL is not seeing the field as empty so not null is not working, I assume this is because it is a text field, but I could be wrong.

 

[LNBruno]

Ok, I think the first thing we need to do is make sure we've got our term definitions the same.

NULL means nothing has EVER been entered into your field (data type of the field doesn't matter).

If a text field has been "blanked out", it isn't NULL, it contains a zero-length string.

So EMPTY to you means...?


< M!ke >
Acupuncture Development: a jab well done.

 

[ptheriault]

First an important service announcement about text fields.

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

Ok,
You are correct you can not search text fields the same way you can other datatypes. You can use the READTEXT function. Look up READTEXT in BOL. Or you should be able to convert it to varchar(max) if you are using SQL 2005.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Ed75]

As far as I know noting has ever been entered into the field but when I run

SELECT FieldName
FROM YourTable
WHERE FieldName IS NOT NULL

I am getting better than 400k results and all of them are apparently empty, nothing visible in the field

 

[LNBruno]

Try testing this:

create table #testing
    (
    somedata text
    , myname varchar(50)
    )

insert #testing
    ( myname )
values
    ( 'm!ke' )

insert #testing
    ( myname )
values
    ( 'ed' )

insert #testing
    ( myname )
values
    ( 'paul' )

insert #testing
    ( somedata )
values
    ( '' ) -- empty string

select * from #testing
where somedata is not null

select * from #testing
where somedata is null

drop table #testing

< M!ke >
Acupuncture Development: a jab well done.

 

[gmmastros]

To return data where the text field is not null or an empty string, you could use the DataLength function.

Select *
From   Table
Where  DataLength([!]TextColumn[/!]) > 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

Darn it, George! That was going to be my next suggestion! LOL! ;-)

< M!ke >
Acupuncture Development: a jab well done.

 

[Ed75]

Select *
From Table
Where DataLength(TextColumn) > 0


PERFECT.... I can't thank you guys enough !!!!

 

[LNBruno]

Well, you could start by clicking the line under George's post that solved your problem that says:

Thank gmmastros for this valuable post!


< M!ke >
Acupuncture Development: a jab well done.