Home » SQL & PL/SQL » SQL & PL/SQL » Usage of INSTR Function
Usage of INSTR Function [message #430792] Thu, 12 November 2009 06:08 Go to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Hi Guys ,

i am using the instr function in a query . For eg a where conditions like -

instr('1~test',concat('1','~'))>0

wokrs fine but it also works fine for

instr('21~test',concat('1','~'))>0

So my issue is that i want use some function or some way out to
match the excat pattern like (1~). I dont want it to get matched in (21~)

Thanks
Suren
Re: Usage of INSTR Function [message #430796 is a reply to message #430792] Thu, 12 November 2009 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You want Regexp_Instr - instr with regular expression matching.
Re: Usage of INSTR Function [message #430798 is a reply to message #430792] Thu, 12 November 2009 06:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Or you could use substr instead.
Re: Usage of INSTR Function [message #430835 is a reply to message #430792] Thu, 12 November 2009 08:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
out of curiosity, why are you using
concat('1','~')
rather than '1'||'~', or even just '1~'
Re: Usage of INSTR Function [message #430902 is a reply to message #430792] Fri, 13 November 2009 00:27 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
My use case i slike this -

i have a data set like below -

lkv_encoded lkv_decoded
1~OTH PSFT Server~Other
1~HOME PSFT Server~Home
1~WORK PSFT Server~Work
21~OTH PSFT Server 848~Other
21~WORK PSFT Server 848~Work
21~BUS PSFT Server 848~Business
21~BB PSFT Server 848~Blackberry
21~HOME PSFT Server 848~Home
1~BB PSFT Server~Blackberry
1~BUS PSFT Server~Business

i am using this query to filter the data -

SELECT lkv_encoded,
lkv_decoded
FROM lkv lkv,
lku lku
WHERE lkv.lku_key = lku.lku_key
AND instr(lkv_encoded, CONCAT('1', '~')) > 0

But when i execute the above query it also brings in the data with 21~ as (1~ is part of 21 ~). I want the query to bring the data with 1~ only not 21~ when i am using CONCAT('1', '~') and to bring the data starting with 21 ~ whne i am using CONCAT('21', '~')


Thanks

Surendra
Re: Usage of INSTR Function [message #430903 is a reply to message #430792] Fri, 13 November 2009 00:33 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
My data set is like -


lkv_encoded lkv_decoded
1~OTH PSFT Server~Other
1~HOME PSFT Server~Home
1~WORK PSFT Server~Work
21~OTH PSFT Server 848~Other
21~WORK PSFT Server 848~Work
21~BUS PSFT Server 848~Business
21~BB PSFT Server 848~Blackberry
21~HOME PSFT Server 848~Home
1~BB PSFT Server~Blackberry
1~BUS PSFT Server~Business
Re: Usage of INSTR Function [message #430904 is a reply to message #430902] Fri, 13 November 2009 00:38 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 SELECT lkv_encoded,
lkv_decoded
FROM lkv
where lkv_encoded like '1~%';

Your answer is not proper for Jrowbottoms question....!
Re: Usage of INSTR Function [message #430905 is a reply to message #430904] Fri, 13 November 2009 00:42 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
I am using CONCAT('1', '~') instead of simply 1~ because the value '1' is not static , it comes dynamicaly from somwhere , its decided at run time .

actually in my query its like CONCAT(KEY_COLUMN, '~'). KEY_COLUMN can have value as 1 , 2 ....anything . One cant predict that .

Thanks
Suren
Re: Usage of INSTR Function [message #430909 is a reply to message #430905] Fri, 13 November 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can nevertheless use:
like KEY_COLUMN||'~%'
which is (I think) easier to read than CONCAT function.

Regards
Michel
Re: Usage of INSTR Function [message #430910 is a reply to message #430792] Fri, 13 November 2009 01:13 Go to previous messageGo to next message
surenhr
Messages: 45
Registered: August 2007
Location: gurgoan
Member
Thanks Guys ,
Smile) I got my solution
Re: Usage of INSTR Function [message #430922 is a reply to message #430902] Fri, 13 November 2009 02:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use either
WHERE substr(lkv_encoded,1,2) = '1~'
which has the advantage of being indexable, or
WHERE regexp_instr(lkv_encoded,'^1~') > 0
Previous Topic: order by given order
Next Topic: Merging records in an output file
Goto Forum:
  


Current Time: Wed Dec 07 06:55:27 CST 2016

Total time taken to generate the page: 0.10986 seconds