distinct with text

[pondi]

Hello all,

2 tables:

elements textfields
------------- ----------------
id (num) -----| typeid (num)
name (nvarchar) |---->ownerid (num)
texto (text)

an element owns a text field like version(typeid=3) or path (typeid=1)...

My tiny query:
"select distinct texto from textfields where typeid=3"
I want to catch all different versions, but it doesn't work because text type doesn't support distinct but i can't change the type, this is not my DB...

Any idea?? thanx a lot!
Note: the DB is SQLServ, but i'd like to link it later with access.

 

[tlbroadbent]

How many characters of the TEXT column do you require to establish uniqueness? You can use the substring function to convert the text to charater and get distinct values up to 8000 characters in SQL Server 2000. I haven't tested this in any other RDMS or SQL Server version.

/* required to display 8000 characters of text */
set textsize 8000
select distinct substring(texto,1,8000)
from textfields where typeid=3

There may be some limitation on the length of the substring so try different values in place of 8000 if that doesn't work for you. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[pondi]

OK thanx it seems to work with SQL7 but not Access97 (trouble with SET). Anymway this is a beginning of solution for me.
With access i tried something too:
"SELECT distinct format(texto) FROM textfields WHERE typeid=3"
Actually I was looking for conversion functions, but I didn't find what format() would do on a text type, it seems to work but I don't know why and how...

Do you know more about format...?
Thx

 

[tlbroadbent]

If you are using Access 97, I recommend the Access forums. Access is not ANSI compliant and you will get answers in this forum that will not work in Access.

Do this in Access.

select distinct left(texto,255)
from textfields where typeid=3
Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin