ODBC Import – Datatype TEXT

[rbma]

Datatype TEXT

This SQL doesn't work in the SQL Where window but works in the Query window:

service_production_description!=''

Here is the error message:

“The text, ntext, and image data types cannot be compared or sorted except when using IS NULL or LIKE”

[Ritesh]

Can you attach or mail me the table structure?

[rbma]

Ritesh wrote on Jul 29 2005, 06:55 PM:

Can you attach or mail me the table structure?

[post=”6639″]<{POST_SNAPBACK}>[/post]

Attached.

[peterlaursen]

BTW

I think the SQL should be:

service_production_description != '' OR service_production_description IS NOT NULL

to test for both empty strings and NULL values.

[rbma]

peterlaursen wrote on Jul 29 2005, 07:03 PM:

BTW

I think the SQL should be:

service_production_description != '' OR service_production_description IS NOT NULL

to test for both empty strings and NULL values.

[post=”6641″]<{POST_SNAPBACK}>[/post]

This statement is not accepted:

service_production_description != ''

This statement is accepted:

service_production_description IS NOT NULL

[peterlaursen]

but the compound statement

service_production_description != '' OR service_production_description IS NOT NULL

what is that?

I don't know too much about string operators in MS SQL syntax but what would this one give:

service_production_description <> ''

and

service_production_description <> '' AND service_production_description IS NOT NULL

Idon't know what this

service_production_description <> ''

returns in MS SQL if variable is NULL (true, NULL or an error)

[rbma]

peterlaursen wrote on Jul 29 2005, 07:23 PM:

but the compound statement

service_production_description != '' OR service_production_description IS NOT NULL

what is that?

Received the error message “The SQL Statement is Invalid”

peterlaursen wrote on Jul 29 2005, 07:23 PM:

service_production_description <> ''

and

service_production_description <> '' AND service_production_description IS NOT NULL

Received the error message “The SQL Statement is Invalid”

[peterlaursen]

hmmmm …

let Ritesh look into it tomorrow!

But my personal belief is that the SQL_WHERE must follow SQL syntax of the source database. When you get these error mesaages it would mean that you are writing wrong syntax. In a lot of respects MySQL syntax is more “relaxed” and “forgiving” than most other SQL-dialects. So if it executes correctly against a mySQL database but not a MS SQL databases that could/must be because of difference in SQL dialects.

Can you execute the same SQL against MS SQL DB-server from a “pure” MS SQL -client without errors ?

BTW when you are talking of “datatype TEXT” is it then the MS SQL datatype TEXT or MySQL datatype TEXT. I understand it as MS SQL. And what a MS SQL TEXT is I really don't know. 🙁

Maybe you'll have to convert to a string using some function for that before comparing with the empty string ''. But you probably know better!

[peterlaursen]

I believe that what I wrote last was correct!

http://msdn.microsoft.com/library/default&#8230;._oa-oz_3qpf.asp

says

Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types.

THAT's WHY both != and <> fail. They can't be used with MS SQL TEXT data types

this:

http://msdn.microsoft.com/library/default&#8230;._ta-tz_3zaq.asp

describes functions to work with texts

I believe you must use

SUBSTRING(text_column, start, length)

SUBSTR() fundction described here

http://msdn.microsoft.com/library/default&#8230;._ta-tz_3zaq.asp

or in your case:

SUBSTRING(service_production_description,0 (or 1),XXXXXXXXX) <> ''

would work.

Maybe you will even have to RTRIM(SUBSTRING(…)) …

I can't find information aabout whether substring trims trailing blanks!

about RTRIM

http://msdn.microsoft.com/library/default&#8230;._ra-rz_6xm5.asp

To also test for NULL values:

SUBSTRING(service_production_description,0 (or 1),XXXXXXXXX) <> '' OR service_production_description IS NOT NULL

XXXXXXXXX won't have to be a high number since it is only used for SQL_WHERE. if you are sure that there are no leading blanks in data “1” will do … Of course you can also LTRIM() …

NOTE that <> is SQL standard != is C-syntax and nonstandard though supported.

VARCHARs in MS SQL can be very long. Do you need TEXTs ? VARCHARS perform much better!

[Author]

Posts