Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!mit-eddie!uw-beaver!ubc-cs!kiwi!joplin!kho
From: kho@joplin.mpr.ca (Peter Kho)
Newsgroups: comp.databases
Subject: Re: substrings ???
Message-ID: <2413@kiwi.mpr.ca>
Date: 31 Oct 90 03:38:15 GMT
References: <9@unigold.UUCP> <DRACK.90Oct26081916@diablo.titan.tsd.arlut.utexas.edu>
Sender: news@eric.mpr.ca
Reply-To: kho@joplin.UUCP (Peter Kho)
Organization: Microtel Pacific Research Ltd., Burnaby, B.C., Canada
Lines: 40

To: lance@unigold.UUCP
Subject: Re: substrings ???
Newsgroups: comp.databases
In-Reply-To: <9@unigold.UUCP>
Organization: Microtel Pacific Research Ltd., Burnaby, B.C., Canada
Cc: 
Bcc: 

In article <9@unigold.UUCP> you write:
>Is there a way to say something like the following in SQL (and in a view
>especially)? [NOT Embeded SQL as I want to do this in a VIEW]
>
>select *
>from this_table
>where this_var=substring(1,4,this_other_string);
>
>this would select only the 1-4 chars of this_other_string and
>be used in the test??
>
>Please do not say RTFM since I don't have any real SQL books around
>and SCO does not include them with Ingres. :(
>
Yes, it's pretty easy
Using Ingres left and right functions as follows:

select *
from table
where column = left(string,3)

left (string, len) and right (string, len).  Using a combination of
lefts and rights, you can extract substrings of any length from any
offset as follows:

left(right(string, size(string) - 4), 5) ---> substring of 5 characters
starting at character 5.


Oracle provides a substring function as substr(string, offset, len).

Have fun.