Home » SQL & PL/SQL » SQL & PL/SQL » How do I check for a number within a string of numbers?
How do I check for a number within a string of numbers? [message #200058] Fri, 27 October 2006 13:03 Go to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

Hi,

Another regexp question.

Basically I have this string of numbers:

'1,2,3,4,5,10,18,25'

And I want to check for the number 8 appearing in the list.

BUT - I do not want to match against 18 eg

'1,8,20,34' = match

'1,18,20,34' = no match

I've had a fiddle about with regular expressions but can't get anything to do this for me.

Can anyone help me out with this?

Fred
Re: How do I check for a number within a string of numbers? [message #200063 is a reply to message #200058] Fri, 27 October 2006 13:55 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

not sure whether this will help u....
select 
    decode(instr('1,8,20,34',',20,'),0,'no match','match')  
    status 
 from dual



Naveen
Re: How do I check for a number within a string of numbers? [message #200084 is a reply to message #200058] Fri, 27 October 2006 15:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Using regex:

SQL> with t as (
  2  select '1,2,3,4,5,10,18,25' id from dual
  3  union all
  4  select '8,10,18,25' from dual
  5  union all
  6  select '1,2,3,4,5,8,10,18,25' from dual
  7  union all
  8  select '1,2,3,4,5,8' from dual)
  9  select id, decode(regexp_instr(id,'(^|\D)8(\D|$)'),0,'NO match','Match') "8's in string" from t;

ID                   8's in string
-------------------- --------------------
1,2,3,4,5,10,18,25   NO match
8,10,18,25           Match
1,2,3,4,5,8,10,18,25 Match
1,2,3,4,5,8          Match

[Updated on: Fri, 27 October 2006 15:51]

Report message to a moderator

Re: How do I check for a number within a string of numbers? [message #200086 is a reply to message #200084] Fri, 27 October 2006 15:58 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1578/0/ ebrian = Regular Expressions guru
Re: How do I check for a number within a string of numbers? [message #200089 is a reply to message #200086] Fri, 27 October 2006 16:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks Littlefoot...but I'm still learning them myself.
Re: How do I check for a number within a string of numbers? [message #200221 is a reply to message #200084] Sun, 29 October 2006 12:13 Go to previous messageGo to next message
Fred Easey
Messages: 73
Registered: January 2005
Member

This works great. Thank you again Smile

Just to check that I understand this right:

'(^|\D)8(\D|$)')

(^|\D)

^ = Start of string
| = OR
\D = Comma

(\D|$)

\D = Comma
| = OR
$ = End of string

So basically before the 8 you must have either the start of the input string or a comma and after the 8 you must have either a comma or the end of the input string.

One question- why did you use \D for comma instead of the comma character itself eg regexp_instr('1,2,18','(^|,)8(,|$)') ?

Fred
Re: How do I check for a number within a string of numbers? [message #200223 is a reply to message #200221] Sun, 29 October 2006 13:09 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You're welcome Fred.

You've got the meaning of ^, $ and | correct, however the \D actually indicates a non-digit character. I used a \D to accommodate the scenario that any spaces would be before or after a number, but if you don't expect any spaces, then using a comma would work just as well.

As you see below, a comma works as long as a space isn't present.

Using \D with a space before a number :

SQL> with t as (
  2    select '1,2,3,4,5,10,18,25' id from dual
  3    union all
  4    select '8,10,18,25' from dual
  5    union all
  6    select '1,2,3,4,5, 8,10,18,25' from dual
  7    union all
  8    select '1,2,3,4,5,8' from dual)
  9    select id, decode(regexp_instr(id,'(^|\D)8(\D|$)'),0,'NO match','Match') "8's in string" from t;

ID                    8's in s
--------------------- --------
1,2,3,4,5,10,18,25    NO match
8,10,18,25            Match
1,2,3,4,5, 8,10,18,25 Match
1,2,3,4,5,8           Match


Using a ',' with a space before a number:

SQL> with t as (
  2    select '1,2,3,4,5,10,18,25' id from dual
  3    union all
  4    select '8,10,18,25' from dual
  5    union all
  6    select '1,2,3,4,5, 8,10,18,25' from dual
  7    union all
  8    select '1,2,3,4,5,8' from dual)
  9    select id, decode(regexp_instr(id,'(^|,)8(,|$)'),0,'NO match','Match') "8's in string" from t;

ID                    8's in s
--------------------- --------
1,2,3,4,5,10,18,25    NO match
8,10,18,25            Match
1,2,3,4,5, 8,10,18,25 NO match
1,2,3,4,5,8           Match


Other than that, your explanation of the full regex is correct.
Re: How do I check for a number within a string of numbers? [message #200230 is a reply to message #200223] Sun, 29 October 2006 15:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or, if you're struggling with regexp, plain-old INSTR will do the trick:

with t as (
   select '1,2,3,4,5,10,18,25' id from dual
   union all
   select '8,10,18,25' from dual
   union all
   select '1,2,3,4,5, 8,10,18,25' from dual
   union all
   select '1,2,3,4,5,8' from dual)
   select id, decode(instr(','||id||',', ',8,'), 0, 'NO match', 'Match') "8's in string" from t;


Ross Leishman
Re: How do I check for a number within a string of numbers? [message #200231 is a reply to message #200230] Sun, 29 October 2006 16:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
rleishman wrote on Sun, 29 October 2006 15:46

with t as (
   select '1,2,3,4,5,10,18,25' id from dual
   union all
   select '8,10,18,25' from dual
   union all
   select '1,2,3,4,5, 8,10,18,25' from dual
   union all
   select '1,2,3,4,5,8' from dual)
   select id, decode(instr(','||id||',', ',8,'), 0, 'NO match', 'Match') "8's in string" from t;




The OP would just have to be sure there won't be any spaces before or after the number:

SQL> with t as (
  2     select '1,2,3,4,5,10,18,25' id from dual
  3     union all
  4     select '8,10,18,25' from dual
  5     union all
  6     select '1,2,3,4,5, 8,10,18,25' from dual
  7     union all
  8     select '1,2,3,4,5,8' from dual)
  9     select id, decode(instr(','||id||',', ',8,'), 0, 'NO match', 'Match') "8's in string" from t;

ID                    8's in s
--------------------- --------
1,2,3,4,5,10,18,25    NO match
8,10,18,25            Match
1,2,3,4,5, 8,10,18,25 NO match
1,2,3,4,5,8           Match

however, could include a REPLACE to take care of any spaces:
SQL> with t as (
  2       select '1,2,3,4,5,10,18,25' id from dual
  3       union all
  4       select '8  ,10,18,25' from dual
  5       union all
  6       select '1,2,3,4,5, 8,10,18,25' from dual
  7       union all
  8       select '1,2,3,4,5,8' from dual)
  9       select id, decode(instr(','||replace(id,' ')||',', ',8,'), 0, 'NO match', 'Match') "8's in string" from t;

ID                    8's in s
--------------------- --------
1,2,3,4,5,10,18,25    NO match
8  ,10,18,25          Match
1,2,3,4,5, 8,10,18,25 Match
1,2,3,4,5,8           Match

Re: How do I check for a number within a string of numbers? [message #200303 is a reply to message #200058] Mon, 30 October 2006 05:10 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi ,

Try using like operator


eg: select * from tablname where columnname like '%8%'


Regards,
Ajay
Re: How do I check for a number within a string of numbers? [message #200305 is a reply to message #200303] Mon, 30 October 2006 05:25 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This would return anything that contains '8' in 'columnname' (including 8, 18, 0.8, abc8xx9, etc.), while OP's request was
Fred Easey
And I want to check for the number 8 appearing in the list.

BUT - I do not want to match against 18
(read the whole post#1 in this topic).
Re: How do I check for a number within a string of numbers? [message #200353 is a reply to message #200058] Mon, 30 October 2006 08:23 Go to previous messageGo to next message
ajaybabu.yaleti
Messages: 11
Registered: October 2006
Junior Member
Hi,

try this

create table test3(a varchar2(30));


select * from test3;

A
------------------------------
122323348
12238348
18238348
12334
812334
01912334
asd098
asd98asdfas
34asdf
34asdf
asdf322378


select a,decode(replace(a,8,' '), a, 'not matched','matched') from test3;

A DECODE(REPL
------------------------------ -----------
122323348 matched
12238348 matched
18238348 matched
12334 not matched
812334 matched
01912334 not matched
asd098 matched
asd98asdfas matched
34asdf not matched
34asdf not matched
asdf322378 matched

or

SQL> select decode(a,a,'matched','notmatched') from test3 t where a like '%8%'
2 and a in (select a from test3 where a = t.a)
3 /

DECODE(A,A
----------
matched
matched
matched
matched
matched
matched
matched


Regards,
Ajay
Re: How do I check for a number within a string of numbers? [message #200360 is a reply to message #200353] Mon, 30 October 2006 08:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You may want to re-read the OP's question. Ross's solution without regex is a perfect solution.

ajaybabu.yaleti wrote on Mon, 30 October 2006 09:23

select a,decode(replace(a,8,' '), a, 'not matched','matched') from test3;

A DECODE(REPL
------------------------------ -----------
122323348 matched
12238348 matched
18238348 matched
12334 not matched
812334 matched
01912334 not matched
asd098 matched
asd98asdfas matched
34asdf not matched
34asdf not matched
asdf322378 matched


No commas included in above data.

ajaybabu.yaleti wrote on Mon, 30 October 2006 09:23

SQL> select decode(a,a,'matched','notmatched') from test3 t where a like '%8%'
2 and a in (select a from test3 where a = t.a)
3 /

DECODE(A,A
----------
matched
matched
matched
matched
matched
matched
matched


This will always come out 'matched'.
Re: How do I check for a number within a string of numbers? [message #200501 is a reply to message #200360] Tue, 31 October 2006 03:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A whole different approach:
Make way Very Happy :
SQL> SELECT v.thestring
  2  FROM ( SELECT '1,2,3,4,5,10,18,25' thestring FROM dual UNION ALL
  3         SELECT '1,8,20,34'          thestring FROM dual UNION ALL
  4         SELECT '1,18,20,34'         thestring FROM dual
  5       ) v
  6  WHERE EXISTS ( SELECT the_value
  7                 FROM table(string_to_tab(v.thestring,','))
  8                 WHERE the_value = 8
  9               )
 10
SQL> /

THESTRING
------------------
1,8,20,34


MHE

[Updated on: Tue, 31 October 2006 03:05]

Report message to a moderator

Re: How do I check for a number within a string of numbers? [message #200544 is a reply to message #200501] Tue, 31 October 2006 05:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This thread is just getting silly now. Are you sure we couldn't write a java plugin to do it for us?
Previous Topic: query help
Next Topic: retrieve records for current month
Goto Forum:
  


Current Time: Mon Dec 05 21:29:56 CST 2016

Total time taken to generate the page: 0.09926 seconds