Finding the Position of a String Within a String
A DBA on IDS 9.40 asked for a function returning the position of the first occurrence of a substring within a string (e.g. 'C' in 'ABCD' = 3). Several workarounds were offered: a user-written SQL/SPL function looping with SUBSTR (posted in full by Doug Lawry), a home-grown charindex/stuff pair modelled on SQL Server, and IBM's regexp DataBlade for V9 (plus the dynamic SPL and Node bladelets). The thread then drifted into broken download links for the regexp bladelet (working links on developerWorks were eventually posted) and an unanswered 'Make: Cannot read or get .' build error on HP-UX.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello All, IDS version 9.40FC6. Does anyone know of a function that will return the position of the first occurrence of a value within a string? For example, something that would return '3' if searching for 'C' in string 'ABCD'. Thanks in advance for any suggestions. Pam Ekstrand Database Administrator OneNeck IT Services 480-315-3087 Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. sending to informix-list
Hello Pam, For 9.21 I have written a function like SQLServer's charindex and stuff. charindex will return the index of a substring within a string like you explained below. charindex('abcd','c') will return 3 charindex ('abcdefc','c',5) will return 7 since it starts looking for 'c' from 5th position 5 only. stuff will replace a string within a string. stuff('abcdefg',3,2,'uvxyz') will return abuvxyzefg. It takes out from position 3, with length 2 and replaces it with the uvxyz. Works with all char, varchar and lvarchar characters upto a maximum of the size of lvarchar character. I will mail the code to you. I have it somewhere in my PC. "Ekstrand, Pam" <Pam.Ekstrand@OneNeck.com> wrote in message news:docvu5$kop$1@news.xmission.com... > > Hello All, > > IDS version 9.40FC6. > > Does anyone know of a function that will return the position of the > first occurrence of a value within a string? > > For example, something that would return '3' if searching for 'C' in > string 'ABCD'. > > Thanks in advance for any suggestions. > > Pam Ekstrand > Database Administrator > OneNeck IT Services > 480-315-3087 > > > Privileged/Confidential Information may be contained in this message or > attachments hereto. Please advise immediately if you or your employer do not > consent to Internet email for messages of this kind. Opinions, conclusions and > other information in this message that do not relate to the official business > of this company shall be understood as neither given nor endorsed by it. > sending to informix-list
Try this:
CREATE FUNCTION search_string (
source VARCHAR(255),
search VARCHAR(255)
) RETURNING
SMALLINT AS position;
DEFINE length_1 SMALLINT;
DEFINE length_2 SMALLINT;
DEFINE position SMALLINT;
LET length_1 = LENGTH(source);
LET length_2 = LENGTH(search);
IF length_2 BETWEEN 1 AND length_1 THEN
FOR position = 1 TO length_1 - length_2 + 1
IF SUBSTR(source, position, length_2) = search THEN
RETURN position;
END IF
END FOR
END IF
RETURN 0;
END FUNCTION;
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"Ekstrand, Pam" <Pam.Ekstrand@OneNeck.com> wrote in message
news:docvu5$kop$1@news.xmission.com...
>
> Hello All,
>
> IDS version 9.40FC6.
>
> Does anyone know of a function that will return the position of the
> first occurrence of a value within a string?
>
> For example, something that would return '3' if searching for 'C' in
> string 'ABCD'.
>
> Thanks in advance for any suggestions.
>
> Pam Ekstrand
> Database Administrator
> OneNeck IT Services
> 480-315-3087
As you are on V9 download the regexp bladelet from IBM, gives you all the regular expresssion searching you could ever need Ekstrand, Pam wrote: > > Hello All, > > IDS version 9.40FC6. > > Does anyone know of a function that will return the position of the > first occurrence of a value within a string? > > For example, something that would return '3' if searching for 'C' in > string 'ABCD'. > > Thanks in advance for any suggestions. > > Pam Ekstrand > Database Administrator > OneNeck IT Services > 480-315-3087 > > > Privileged/Confidential Information may be contained in this message or > attachments hereto. Please advise immediately if you or your employer do not > consent to Internet email for messages of this kind. Opinions, conclusions and > other information in this message that do not relate to the official business > of this company shall be understood as neither given nor endorsed by it. > sending to informix-list
Paul Watson wrote: > As you are on V9 download the regexp bladelet from IBM, gives you all > the regular expresssion searching you could ever need > Yup, cool, great. Try to donwload it and you get 404 for the URL http://www7b.boulder.ibm.com/vadd-bin/httpdl?1/vadc/dmdd/informix/regexp.1.0.tar.Z after you accept the terms and conditions. Superb. Any clues as to where I can get it? malc
You could ask your valued IBM Business Partner ;-) -- Neil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com <malc_p@btinternet.com> wrote in message news:1135254276.834280.111730@g14g2000cwa.googlegroups.com... > > Paul Watson wrote: >> As you are on V9 download the regexp bladelet from IBM, gives you all >> the regular expresssion searching you could ever need >> > > Yup, cool, great. > Try to donwload it and you get 404 for the URL > http://www7b.boulder.ibm.com/vadd-bin/httpdl?1/vadc/dmdd/informix/regexp.1.0.tar.Z > after you accept the terms and conditions. > > Superb. > > Any clues as to where I can get it? > > malc >
......or I could ask you ;-) Seasonal felicitations! OK Neil, I'll bite - where can I get regexp, and will it work on HP-UX?
How do you get to that link?
malc_p@btinternet.com wrote: > .......or I could ask you ;-) > Seasonal felicitations! > OK Neil, I'll bite - where can I get regexp, and will it work on HP-UX? > It is on the IDN site, or was, if you can't find it then I'll post a copy to you. Yes it works under HPUX but you will need a C compiler The regexp bladelet, the dynamic SPL bladelet and Node bladelet should, IMHO, be included in every V9 installation - just makes life easier Cheers Paul
Do you happen to know one? Neil Truby wrote: > You could ask your valued IBM Business Partner ;-) > > -- > Neil Truby t:01932 724027 > Director m:07798 811708 > Ardenta Limited e:neil.truby@ardenta.com > > > <malc_p@btinternet.com> wrote in message > news:1135254276.834280.111730@g14g2000cwa.googlegroups.com... > > > > Paul Watson wrote: > >> As you are on V9 download the regexp bladelet from IBM, gives you all > >> the regular expresssion searching you could ever need > >> > > > > Yup, cool, great. > > Try to donwload it and you get 404 for the URL > > http://www7b.boulder.ibm.com/vadd-bin/httpdl?1/vadc/dmdd/informix/regexp.1.0.tar.Z > > after you accept the terms and conditions. > > > > Superb. > > > > Any clues as to where I can get it? > > > > malc > >
Hi David I just did a search for "regexp" from the www.informix.com home page (aka www-306.ibm.com/software/data/informix/) which takes you to article www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/db_regexp.html which has this link which should take you to the download in the "Getting Started" section. *** STOP PRESS *** There's also a link on //www-128.ibm.com/developerworks/db2/zones/informix/library/samples/db_downloads.html which I've just found that works. I'll report back on progress........
Ok - first hurdle: Make for morons? (not allowed to say "make for dummies" or I'll get sued. Oh gosh darn I just did. Shucks). Can anyone explain what "Make: Cannot read or get ." - that's a full stop/period at the end - means and how I get past it? Thanks Malc
Are you related to the SG Palmer quoted on the Ardenta website as sayiong how wonderful we are? "Simon" <si_g_palmer@yahoo.com> wrote in message news:1135329748.943559.64230@f14g2000cwb.googlegroups.com... > Do you happen to know one? > > > > Neil Truby wrote: >> You could ask your valued IBM Business Partner ;-) >> >> -- >> Neil Truby t:01932 724027 >> Director m:07798 811708 >> Ardenta Limited e:neil.truby@ardenta.com >> >> >> <malc_p@btinternet.com> wrote in message >> news:1135254276.834280.111730@g14g2000cwa.googlegroups.com... >> > >> > Paul Watson wrote: >> >> As you are on V9 download the regexp bladelet from IBM, gives you all >> >> the regular expresssion searching you could ever need >> >> >> > >> > Yup, cool, great. >> > Try to donwload it and you get 404 for the URL >> > http://www7b.boulder.ibm.com/vadd-bin/httpdl?1/vadc/dmdd/informix/regexp.1.0.tar.Z >> > after you accept the terms and conditions. >> > >> > Superb. >> > >> > Any clues as to where I can get it? >> > >> > malc >> > >