LIKE, REGEX

[Patrice FRITSCH]

Hi,

Using the following records :
PF03001
PF03002
PF03003
PF03004
PFR03001
PFR03002
PFR03003
PFR03004

How can I extract only the PF records (and not the PFR ones) using the LIKE
statement ?
My first idea was to search for PF and verify that the following char is a
numeric one :
"select ... where ID like 'PF[0-9]%'"
Of course, it does not work :-(

I'm using an Oracle database !
I search for regex but did not found a lot !

TIA

Patrice

[Brandon Miller]

Select MyField from MyTable
WHERE
SUBSTRING(MyField, 1, 2) = 'PF')
AND SUBSTRING(MyField, 1, 3) <> 'PFR')

"Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
news:%23l2sFjURDHA.940@TK2MSFTNGP11.phx.gbl...

> Hi,
>
> Using the following records :
> PF03001
> PF03002
> PF03003
> PF03004
> PFR03001
> PFR03002
> PFR03003
> PFR03004
>
> How can I extract only the PF records (and not the PFR ones) using the

LIKE

> statement ?
> My first idea was to search for PF and verify that the following char is a
> numeric one :
> "select ... where ID like 'PF[0-9]%'"
> Of course, it does not work :-(
>
> I'm using an Oracle database !
> I search for regex but did not found a lot !
>
> TIA
>
> Patrice
>
>

[Kris Eiben]

How about: Where ID like 'PF%' and ID not like 'PFR%'
Hmm, might be slow, with two like clauses. Maybe worth a try, at least.

"Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
news:#l2sFjURDHA.940@TK2MSFTNGP11.phx.gbl...

> Hi,
>
> Using the following records :
> PF03001
> PF03002
> PF03003
> PF03004
> PFR03001
> PFR03002
> PFR03003
> PFR03004
>
> How can I extract only the PF records (and not the PFR ones) using the

LIKE

> statement ?
> My first idea was to search for PF and verify that the following char

is a

> numeric one :
> "select ... where ID like 'PF[0-9]%'"
> Of course, it does not work :-(
>
> I'm using an Oracle database !
> I search for regex but did not found a lot !
>
> TIA
>
> Patrice
>
>

[Patrice FRITSCH]

Thanks all for your quick answers.
Your solutions would work for PF & PFR, but I forgot to mention something!!
SORRY
In fact, we have always a userid (2 or more chars) followed by a serial
number. And of course we have hundreds of userId's ! So it may happen that
one userid starts with the one of another users (in my example PFR & PF).

So I have to be sure that my fields starts with the usersId + any numeric
value, that's why I was looking for something like "UserId"[0-9]%

I'm not sure I'm clear :-(

"Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> a écrit dans le message de
news: #l2sFjURDHA.940@TK2MSFTNGP11.phx.gbl...

> Hi,
>
> Using the following records :
> PF03001
> PF03002
> PF03003
> PF03004
> PFR03001
> PFR03002
> PFR03003
> PFR03004
>
> How can I extract only the PF records (and not the PFR ones) using the

LIKE

> statement ?
> My first idea was to search for PF and verify that the following char is a
> numeric one :
> "select ... where ID like 'PF[0-9]%'"
> Of course, it does not work :-(
>
> I'm using an Oracle database !
> I search for regex but did not found a lot !
>
> TIA
>
> Patrice
>
>

[Aaron Bertrand - MVP]

> "select ... where ID like 'PF[0-9]%'"

> Of course, it does not work :-(
>
> I'm using an Oracle database !

This [0-9] syntax will work in SQL Server. I think you need query help from
an Oracle newsgroup, not an ASP newsgroup!

A

[Patrice FRITSCH]

You are absolutely right Ray.
Unfortunately I have a read-only access to the databases and cannot change
anything as this tables are used by other applications !!!

I guess the solution is to verify in my ASP page and not solve the problem
with a SQL query :-(

"Ray at <%=sLocation%>" <ask@me.forit> a écrit dans le message de news:
[email]uNzAcmVRDHA.560@TK2MSFTNGP10.phx.gbl[/email]...

> I'm not sure you're clear either. [: But it sounds like you have two
> pieces of independent data stored in a single column, and if that's the
> case, these kinds of problems are inevitable. Perhaps you should consider
> splitting the data.
>
> Ray at work
>
> "Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
> news:eGI1FKVRDHA.1804@TK2MSFTNGP11.phx.gbl...

> > Thanks all for your quick answers.
> > Your solutions would work for PF & PFR, but I forgot to mention

> something!!

> > SORRY
> > In fact, we have always a userid (2 or more chars) followed by a serial
> > number. And of course we have hundreds of userId's ! So it may happen

that

> > one userid starts with the one of another users (in my example PFR &

PF).

> >
> > So I have to be sure that my fields starts with the usersId + any

numeric

> > value, that's why I was looking for something like "UserId"[0-9]%
> >
> > I'm not sure I'm clear :-(
> >
> > "Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> a écrit dans le message

de

> > news: #l2sFjURDHA.940@TK2MSFTNGP11.phx.gbl...

> > > Hi,
> > >
> > > Using the following records :
> > > PF03001
> > > PF03002
> > > PF03003
> > > PF03004
> > > PFR03001
> > > PFR03002
> > > PFR03003
> > > PFR03004
> > >
> > > How can I extract only the PF records (and not the PFR ones) using the

> > LIKE

> > > statement ?
> > > My first idea was to search for PF and verify that the following char

is

> a

> > > numeric one :
> > > "select ... where ID like 'PF[0-9]%'"
> > > Of course, it does not work :-(
> > >
> > > I'm using an Oracle database !
> > > I search for regex but did not found a lot !
> > >
> > > TIA
> > >
> > > Patrice
> > >
> > >

> >
> >

>
>

[Aaron Bertrand - MVP]

> I guess the solution is to verify in my ASP page and not solve the problem

> with a SQL query :-(

Or, see an ORACLE NEWSGROUP to get help with your query?!??!?

[Ray at]

Well, in theory, it shouldn't matter what the data source is, since SQL is
the same regardless of what kind of database you're using. You did hear my
sarcastic tone there, yes? :P

Ray at work

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:egg933VRDHA.2676@TK2MSFTNGP10.phx.gbl...

> > I guess the solution is to verify in my ASP page and not solve the

problem

> > with a SQL query :-(

>
> Or, see an ORACLE NEWSGROUP to get help with your query?!??!?
>
>

[Chris Hohmann]

"Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
news:eGI1FKVRDHA.1804@TK2MSFTNGP11.phx.gbl...

> Thanks all for your quick answers.
> Your solutions would work for PF & PFR, but I forgot to mention

something!!

> SORRY
> In fact, we have always a userid (2 or more chars) followed by a

serial

> number. And of course we have hundreds of userId's ! So it may happen

that

> one userid starts with the one of another users (in my example PFR &

PF).

>
> So I have to be sure that my fields starts with the usersId + any

numeric

> value, that's why I was looking for something like "UserId"[0-9]%

Use Brandon's solution, but reference UserID instead of hard coding 'PF'

HTH
-Chris

[Chris Hohmann]

Responses inline:
"Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
news:unfs9UeRDHA.304@tk2msftngp13.phx.gbl...

> Hello Chris,
>
> It will not work !

No need to shout.

> My Sql queryString will be :
> sqQuery = "Select ...... from MyTable where ID like '" +
> request.queryString("UserId") + "%'"
>
> As the userid coming from my QueryString can have 2 or more chars
>
> If I have a userId PH, it will also return the PHB, PHM, PHTI, PH...

records

> !

Every see that Seinfeld episode with Elaine and exclamation points? "I
would use one here!, and here! and here!"

> I have modified my asp code to verify that the char following the

UserId in

> the ID field is a numeric one.
>
> When searching for userId PH, I will also receive the PHB%, PHM%,

PHTI%

> records.
> I just have to test that the 3rd char of the return field is numeric.
>

sqQuery = _
"SELECT ...... FROM MyTable" & vbCRLF &_
"WHERE ID >= '" & Request.QueryString("UserId") & "0'" & vbCRLF &_
"AND ID < '" & Request.QueryString("UserId") & ":'"

> Thank anyway for your help
> Patrice

Your welcome.
-Chris

P.S. I found this solution in the first hit I got from Google Groups
using "oracle regular expression" as the search term.

[Chris Hohmann]

I really have to get out of the habit of replying to my own posts,
but...

Here's a really interesting discussion on building regular expression
support into Oracle.

[url]http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2200894550208[/url]

[Patrice FRITSCH]

Hello Chris

I DID NOT SHOUT :-)))

your solution is excellent.
Meanwhile I found another solution one using the SUBSTR function.
"SELECT ... FROM MyTable WHERE ID like '" & szId & "%' AND SUBSTR(ID, " &
len(szId)+1 & ", 1) IN .......

I will also have a look at the oracle newsgroup, as the regular expression
solution can be usefull for other applications.
Thanks again Chris


"Chris Hohmann" <hohmannATyahooDOTcom> a écrit dans le message de news:
[email]uEMuAikRDHA.3700@tk2msftngp13.phx.gbl[/email]...

> Responses inline:
> "Patrice FRITSCH" <patrice.fritsch@NOSPAM.com> wrote in message
> news:unfs9UeRDHA.304@tk2msftngp13.phx.gbl...

> > Hello Chris,
> >
> > It will not work !

>
> No need to shout.
>

> > My Sql queryString will be :
> > sqQuery = "Select ...... from MyTable where ID like '" +
> > request.queryString("UserId") + "%'"
> >
> > As the userid coming from my QueryString can have 2 or more chars
> >
> > If I have a userId PH, it will also return the PHB, PHM, PHTI, PH...

> records

> > !

>
> Every see that Seinfeld episode with Elaine and exclamation points? "I
> would use one here!, and here! and here!"
>

> > I have modified my asp code to verify that the char following the

> UserId in

> > the ID field is a numeric one.
> >
> > When searching for userId PH, I will also receive the PHB%, PHM%,

> PHTI%

> > records.
> > I just have to test that the 3rd char of the return field is numeric.
> >

>
> sqQuery = _
> "SELECT ...... FROM MyTable" & vbCRLF &_
> "WHERE ID >= '" & Request.QueryString("UserId") & "0'" & vbCRLF &_
> "AND ID < '" & Request.QueryString("UserId") & ":'"
>

> > Thank anyway for your help
> > Patrice

>
> Your welcome.
> -Chris
>
> P.S. I found this solution in the first hit I got from Google Groups
> using "oracle regular expression" as the search term.
>
>