Returning a partial string

[Ruslan Zasukhin]

On 7/7/05 10:45 PM, "jda" <jda at his.com> wrote:

Hi John,

> I have a text field with a list of names, separated by return characters.
> 
> I'd like to be able to sort by just the *1st* name, ignoring the
> others. Of course, if there is just one name (which would not end in
> a return) I want to sort by that.
> 
> How to put this all in one sql statement?
> 
> SELECT * from table ORDER BY Left(textField, (LOCATE(chr(13), textfield)))
> 
> looks like it might retrieve the first name, but would get nothing if
> there was no return char.
> 
> Any ideas?

First of all, I'd suggest put this formula into Table Method,
Because they you get permanent index on it.

Second, I think it needs put formula into SELCT
And use 
   
    ORDER 12

Or give it in select alias name


SELECT Left(textField, (LOCATE(chr(13), textfield))) as 'aaa'
FROM T
ORDER BY aaa



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]