Testing text field for content

Question

Testing text field for content

homer

Newbie

Points: 7
More actions
May 16, 2003 at 7:36 am

#80229

How can I test a text field to see if it contains any characters in it ?
Edited by - homer on 05/16/2003 08:26:37 AM

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

if you mean by comment something like '/', '//', '/*' in your textfield you can use CHARINDEX or PATINDEX to check for existance. See BOL for details.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Oops,

my mistake!

Should have been reading more careful. You cannot use CHARINDEX on textfields. I'll keep searching.

Cheers,

Frank

Edited by - a5xo3z1 on 05/16/2003 08:27:51 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi,

another try,

DECLARE @text varchar(8000)

SELECT @text = <your_textfield> FROM <table>

IF len(@text)>0

PRINT 'HALLO'

ELSE

PRINT 'SCHADE'

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi,

well, after thinking about this over the weekend, just my 2 cents.

Can't you just check for IS NULL?

I've tried this on one of my textfields and it seems to work.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65144 More actions · Answer 5

You could use

where datalength(textfield) > 0

p.s. I disagree with 'You cannot use CHARINDEX on textfields', I can on SQL7 SP4.

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi David,

quote:
p.s. I disagree with 'You cannot use CHARINDEX on textfields', I can on SQL7 SP4.

From BOL (SQL2k)

...

These functions and statements can be used with ntext, text, or image data.

Functions

DATALENGTH

PATINDEX

SUBSTRING

TEXTPTR

TEXTVALID

...

...but it seems to work in some cases?!?

Anyway, using DATALENGTH is a good solution. Thanks for this.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65144 More actions · Answer 7

Thanks for that Frank, will bear it in mind. We are still on SQL7 but now looking at future strategy for SQL.

Far away is close at hand in the images of elsewhere.
Anon.

homer Newbie Points: 7 More actions · Answer 8

quote:
You could use
where datalength(textfield) > 0
p.s. I disagree with 'You cannot use CHARINDEX on textfields', I can on SQL7 SP4.

Thanks for this one--Work great, less filling too