jkj2001
Posts: 142 Joined: Fri Mar 29, 2002 1:39 pm
Post
by jkj2001 » Thu Nov 04, 2004 7:09 pm
Hi all,
We're using version 4.04.1067366033 of texis here, and I was wondering how I could go about determining the largest value sitting in a given column?
For instance, say I have an Author field; I'd like to know who has the longest name and how many characters it is. Thanks!
John
Site Admin
Posts: 2622 Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:
Post
by John » Thu Nov 04, 2004 10:38 pm
select max(length(Author)) from Table
will give you the length of the longest Author. That doesn't give you the name of that Author. You might also want:
select Author, length(Author) from Table order by 2 desc
will list all the authors by length of name, so if you just pluck off the first value you'll have a name.
John Turnbull
Thunderstone Software
jkj2001
Posts: 142 Joined: Fri Mar 29, 2002 1:39 pm
Post
by jkj2001 » Fri Nov 05, 2004 1:56 pm
Thanks John!
jkj2001
Posts: 142 Joined: Fri Mar 29, 2002 1:39 pm
Post
by jkj2001 » Fri Nov 12, 2004 2:13 pm
Hi again,
This function works great...except when I tried it on a blob field. Is there a similar method I can use to find the max length in that column?
mark
Site Admin
Posts: 5519 Joined: Tue Apr 25, 2000 6:56 pm
Post
by mark » Fri Nov 12, 2004 2:50 pm
Try converting it to varchar first convert(Author,'varchar')
jkj2001
Posts: 142 Joined: Fri Mar 29, 2002 1:39 pm
Post
by jkj2001 » Fri Nov 12, 2004 4:01 pm
Thanks-- I got what I needed by running a select and ordering by length, in descending sequence.
For some reason though, when I tried using max --as in "max(length(convert(myblob,'varchar')))"-- I received an "ABEND: signal 11" error? Not sure why, it cropped up immediately after the tsql command.
mark
Site Admin
Posts: 5519 Joined: Tue Apr 25, 2000 6:56 pm
Post
by mark » Fri Nov 12, 2004 5:57 pm
ABEND means texis had problems handling that query. What version of texis?