Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE - using field name as part of regular expression? (Oracle 10g)
REGEXP_LIKE - using field name as part of regular expression? [message #302899] Wed, 27 February 2008 05:14 Go to next message
dkay
Messages: 1
Registered: May 2005
Junior Member
Hi

I am writing a stored procedure that will be using address line strings as input and then try and do a pattern match against a table containing country names.

So my COUNTRY table looks like:

ID NAME
--- ---------
1 ITALY
2 AUSTRALIA

The string being searched will be something like "SIDNEY, AUSTRALIA".

However, my regular expression needs to be a field name plus $.

Is there any way that this can work? I've looked in a number of websites but have found no examples using a field name as part of the regular expression.

I have been able to get this to work using:
SELECT ID, NAME
FROM COUNTRY
WHERE 'SIDNEY, AUSTRALIA' LIKE '%' || NAME;

Can I use something like this:
SELECT ID, NAME
FROM COUNTRY
WHERE REGEXP_LIKE('SIDNEY, AUSTRALIA', 'NAME$');

I know the above doesn't work, how can I fix it?

thx

David
Re: REGEXP_LIKE - using field name as part of regular expression? [message #302904 is a reply to message #302899] Wed, 27 February 2008 05:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To be honest I don't understand how or why you claim your first query works. Because
  1  with t
  2  as
  3  (select 1 id, 'ITALY' country from dual
  4   union all
  5   select 2, 'AUSTRALIA' from dual
  6  )
  7  select id, country from t
  8* where 'ITALY, AUSTRALIA' like '%' || country
SQL> /

        ID COUNTRY
---------- ---------
         2 AUSTRALIA


I will expect to get both the records back but I received only one. So what I think is you need a varying in-list. Search in this site.

Also take some time to read the forum guidelines.

HTH

Regards

Raj

Re: REGEXP_LIKE - using field name as part of regular expression? [message #303073 is a reply to message #302899] Wed, 27 February 2008 21:41 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from country;

        ID NAME
---------- --------------------
         1 ITALY
         2 AUSTRALIA

SQL> SELECT ID, NAME
  2  FROM COUNTRY
  3  WHERE REGEXP_LIKE('SIDNEY, AUSTRALIA', name, 'i');

        ID NAME
---------- --------------------
         2 AUSTRALIA

SQL> SELECT ID, NAME
  2  FROM COUNTRY
  3  WHERE REGEXP_LIKE('ITALY, AUSTRALIA', name, 'i');

        ID NAME
---------- --------------------
         1 ITALY
         2 AUSTRALIA

If case sensitivity isn't a concern, you can omit the 'i' option above.
Previous Topic: Returning only the first12 records returned by a query
Next Topic: is it possible to switch between tables in query ?
Goto Forum:
  


Current Time: Sat Dec 10 07:10:23 CST 2016

Total time taken to generate the page: 0.06480 seconds