f



Text search

Hi *

following situation

a table with a column ID varchar2(4000)

I want to search with an ID-Range like

select * from table where id between '12' and '1234AABBG'

Does this work and what about NLS- dependent information ?


Thanx for your help

Ralph
0
Ralph
8/15/2006 4:59:56 PM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

3 Replies
411 Views

Similar Articles

[PageSpeed] 28

Ralph schreef:
> Hi *
> 
> following situation
> 
> a table with a column ID varchar2(4000)
> 
> I want to search with an ID-Range like
> 
> select * from table where id between '12' and '1234AABBG'
> 
> Does this work and what about NLS- dependent information ?
> 

No - what whould be "in between" ?!?

Look up NLS_SORT in the manual at tahiti.oracle.com

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
0
Frank
8/15/2006 5:49:03 PM
Frank van Bortel wrote:

> Ralph schreef:
> 
>> Hi *
>>
>> following situation
>>
>> a table with a column ID varchar2(4000)
>>
>> I want to search with an ID-Range like
>>
>> select * from table where id between '12' and '1234AABBG'
>>
>> Does this work and what about NLS- dependent information ?
>>
> 
> No - what whould be "in between" ?!?
> 
> Look up NLS_SORT in the manual at tahiti.oracle.com
> 

Assume id has value of 12NNXXXXX where NN are numbers and XX are chars.
the between should define a range of all Id's "between" 12 amd 
1234AABBGG. Maybe regular expressions can make it ? !

0
Ralph
8/15/2006 6:04:20 PM
Ralph schreef:

> Frank van Bortel wrote:
>
> > Ralph schreef:
> >
> >> Hi *
> >>
> >> following situation
> >>
> >> a table with a column ID varchar2(4000)
> >>
> >> I want to search with an ID-Range like
> >>
> >> select * from table where id between '12' and '1234AABBG'
> >>
> >> Does this work and what about NLS- dependent information ?
> >>
> >
> > No - what whould be "in between" ?!?
> >
> > Look up NLS_SORT in the manual at tahiti.oracle.com
> >
>
> Assume id has value of 12NNXXXXX where NN are numbers and XX are chars.
> the between should define a range of all Id's "between" 12 amd
> 1234AABBGG. Maybe regular expressions can make it ? !

What would be NLS dependent for a 9999AAAAAA format?
Once more, lookup nls_sort, choose the one you want, and alter your
session
to use that. Apart from that, it works - use order by to check for
anomalies

0
Frank
8/16/2006 1:25:21 PM
Reply:

Similar Artilces:

Database vs Text for text search
At what level a database will be better for a read only text search? I have some records, each has 6 fields. Each record has a pararent record. Each parent has less than 100 childs. Total records is about 1000-1500. I want to put everthing in one single file, or put each parent and their children in a file so I can have 20-30 files. I am afraid to go to the database route, because the limitation of concurent connections and the overhead of database search. Is there a cut-off number of records, below that line, text is better, above it, database is better? "http://links.i6networks...

Search for text in Database
Hi, I would like to use keywords inside a record to search for - searchengine-functionality How can I design the table? Right now I would store data in CLOB/BLOB or Memo-Column in a format like ";apple;banana;lemon;". I would use a INSTR(ring)-function - but I wonder if the performance is still OK then - even when I index the field. Are there maybe even better ways? Thanx in advance. Greg Greg Cyrus wrote: >Hi, >I would like to use keywords inside a record to search for - >searchengine-functionality >How can I design the table? Right now I would...

Search Text in Database
Hi, I would like to use keywords inside a record to search for - like a searchengine. How can I design the table? Right now I would store data in Memo-Column in a format like ";apple;banana;lemon;". I would use a INSTR(ring)-function - but I wonder if the performance is still OK then - even when I index the field. Are there maybe even better ways? Thanx in advance. Greg Each field in a given row should store one piece of information. Seperating your data with semicolons suggests that your storing multiple piecies of data in a field that should only be storing a single pei...

help searching for oracles and Oracles and ORACLES
Hello Mark D Powell, Robert Klemme, Michael O'Shea, David FitzJarrell and thank you for your help before. I have a table called "table2" that has the following in it id1 Description 1 oracle oracle oracle oracle 2 oracle school oracle school school school 6 the school the party the oracle How can I get SQL to get rows with Oracle or oracle (first letter is upcase) or ORACLE My SQL is select * from table2 where description like '%oracle%' or description like '%Oracle' or description like '%ORACLE%' In access '%oracle%' works in upcase or lower case but in Oracle I have to keep testing for all cases. The word might be oRaCLe but I don't want to write them all. How can I do this Thank you Jon Hi Jon, I would suggest the following: select * from table2 where instr(lower(description),'oracle') > 0; lower will make all the characters in the description field lowercase so you only need to test for lower case characters. instr will return > 0 if it finds any occurance of the word 'oracle' Hope this helps, C On Feb 18, 3:44=A0pm, jharbo...@googlemail.com wrote: > Hello Mark D Powell, Robert Klemme, Michael O'Shea, David FitzJarrell > and thank you for your help before. > > I have a table called "table2" that has the following in it > > id1 =A0 =A0 =A0Description > =A01 =A0 =A0...

Oracle Text Search Issue
Hi All, I've a table and created a Full Text index on the same as follows: CREATE TABLE FT_TEST ( COL1 NUMBER(3), COL2 VARCHAR2(100 BYTE) ); CREATE INDEX IDX_FT_COL2 ON FT_TEST(COL2) INDEXTYPE IS CTXSYS.CONTEXT; INSERT INTO FT_TEST VALUES(1, 'nt=a;puc=s'); INSERT INTO FT_TEST VALUES(2, 'nt=a;puc=t'); INSERT INTO FT_TEST VALUES(3, 'nt=a;puc=a'); EXEC CTX_DDL.SYNC_INDEX('IDX_FT_COL2'); When I issue a query like - SELECT * FROM FT_TEST WHERE CONTAINS(COL2, '{puc=s}') > 1 COL1 COL2 1 nt=a;puc=s 2 nt=a;puc=t 3 nt=a;puc=a I was expect...

Carrying out a full-text search, but without the full-text search...
Hi everyone. I have a fairly simple question, but the answer is likely to be very complex. I would like to create a wiki that will allow full-text searches. However, as you know, full-text searches in a large database is very CPU intensive. I need to look into what options are available in the way of breaking everything down into keywords, and how I'd do it efficiently. I know that there is no definitive answer to my query, but I have no idea where to start looking. I would like to make my wiki very much like a small-scale search engine. Much like Google, but far, far smaller by compariso...

DataBase DataBase DataBase DataBase
DataBase DataBase DataBase DataBase Porfessional Programmable Database Ver. 2.0 2.1 Million Record Capacity. Search Rate: 2000/Records/Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Easy to Learn (Typically 30 Seconds) Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). Easy Paypal Online Ordering. See Site Below. http://www.vehiclerepair.org/dbPro/dbpro.html Scott: #DataBase...

DataBase DataBase DataBase DataBase
DataBase DataBase DataBase DataBase Porfessional Programmable Database Ver. 2.0 2.1 Million Record Capacity. Search Rate: 2000/Records/Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Easy to Learn (Typically 30 Seconds) Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). Easy Paypal Online Ordering. See Site Below. http://www.vehiclerepair.org/dbPro/dbpro.html ...

Oracle Text Vs. other Search Products
Hello, I'm looking for expert advice regarding text mining (keyword searches and smart searches (subject/object/verb/synonyms) within large text objects. Apparently there are a number of products out there like ISYS-Search and Goldfire that can be used to perform indexed searches on very large amounts of data. I am trying to determine whether Oracle's Text functionality is comparable to these packages? I am also trying to determine whether data should be stored within BLOB or CLOB fields within Oracle or as text files on a file server. Thanks mvbokker wrote: > Hello, > > I'm looking for expert advice regarding text mining (keyword searches > and smart searches (subject/object/verb/synonyms) within large text > objects. > > Apparently there are a number of products out there like ISYS-Search > and Goldfire that can be used to perform indexed searches on very large > amounts of data. I am trying to determine whether Oracle's Text > functionality is comparable to these packages? > > I am also trying to determine whether data should be stored within BLOB > or CLOB fields within Oracle or as text files on a file server. > > Thanks Is the text strictly ASCII characters? No formatting? Or are you wanting to store .pdf files and search within them? Are you looking for hit-highlighting? More information is required to help you. -- Daniel A. Morgan http://www.psoug.org damor...

SQL searches and ODBC to Oracle database
Where in Access are the features to run SQL queries and view tables on an Oracle server? Thanks. The usual way to do this is to "link" to the Oracle tables via ODBC, so that they appear in the Access table list (with special icons, to indicate that they are just links to the real tables). After that, you can compose queries in Access in the usual way. If you have some special queries or need to use Oracle functionality in your query SQL, create a "pass-through" query in Access. You can find more detailed info on this stuff in the Help file. Hope this helps, Anne &qu...

Oracle Text and searches across columns
Say if I am making a database of college classes across many colleges, where people can search and find classes. So, for example, each class has a: level - bachelors, graduate (for example) college name- State U, or SU as short name, along with other colleges semester - Fall 2008... Category - Biology, Math, Music etc. course number - BIO 101 description - a full text description of the course. etc. as you can imagine, all of these things are in many tables in many columns. The "full description" that the user sees actually contains information from a join of many columns. Wha...

is OCI API for accessing Oracle database, and Oracle RAC (database cluster) same or different?
Hi, My application uses OCI to access Oracle database. Now, I came to know about the Oracle RAC failover kind of configuration. Is OCI API for accessing an Oracle cluster is same or different? Is my existing application works well with a cluster (taking the advantage of failover) or do I need to do any changes in my application? Thanks in advance, Ramakrishna. On 14 Jun 2006 23:41:29 -0700, "RamaKrishna Narla" <rknarla@gmail.com> wrote: >Hi, > >My application uses OCI to access Oracle database. >Now, I came to know about the Oracle RAC failover kind of >configuration. Is OCI API for accessing an Oracle cluster is same or >different? Is my existing application works well with a cluster (taking >the advantage of failover) or do I need to do any changes in my >application? > >Thanks in advance, >Ramakrishna. It makes no difference, unless the instance parameters are different. -- Sybrand Bakker, Senior Oracle DBA Sybrand Bakker wrote: > > It makes no difference, unless the instance parameters are different. > > -- > Sybrand Bakker, Senior Oracle DBA Hi Sybrand, Thanks for giving reply on this. Can you please elaborate your response. Is Instance parameters mean Global Database Name, user name and password? Thanks, Ramakrishna. On 15 Jun 2006 01:37:28 -0700, "RamaKrishna Narla" <rknarla@gmail.com> wrote: > >Sybrand Bakker wrote...

oracle options for full text searching?
What are my best options for doing some full text searching in an Oracle database? As a concrete example of what I'm doing, I'm pulling out image metadata (including descriptions) from various sources such as digital camera photos, quicktime movie clips, etc., and would like to be able to efficiently search on the extracted information. Many TIA! Mark -- Mark Harrison Pixar Animation Studios Mark Harrison wrote: > What are my best options for doing some full text searching > in an Oracle database? > > As a concrete example of what I'm doing, I'm pulling out > image metadata (including descriptions) from various sources > such as digital camera photos, quicktime movie clips, etc., > and would like to be able to efficiently search on the > extracted information. > > Many TIA! > Mark InterMedia and UltraSearch -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org ...

Search efficiency
Hi everyone, I just had an interesting discussion with a colleague regarding how we are implementing search. We allow users to browse resources under a list of topics, so for example a list of all resources relating to 'cars'. One way of doing this is with a SQL query against the database backend: SELECT * FROM resources WHERE topic_area = 'cars' The actual query is quite a bit more complicated than this (involves table joins and the like). However, he said that due to the table joins (coming from the fact that the database is normalised), that this technique ...

Best text search for many Gigabyte Database?
I have a so big DB, It's about 10-100GB of text. Now I want to create a searching function but It take too long when I use LIKE from MySQL Anyone know the best sollution? Can I use Google Desktop search or an others? Please help! ngocviet wrote: > I have a so big DB, It's about 10-100GB of text. > Now I want to create a searching function but It take too long when I > use LIKE from MySQL > Anyone know the best sollution? > Can I use Google Desktop search or an others? > Please help! If you use mySQL, consider using FULLTEXT and MATCH AGAINST(). Bottomline stays: I...

Help understanding an oracle text search term
I have a varchar2 column (col2) and I've created an CTXSYS.CONTEXT index for it (using the empty stoplist). I'm running a query that has ... AND CONTAINS(col2, 'G"%') > 0 This results in an error "DRG-51030: wildcard query expansion resulted in too many terms" But if I change the search term to 'G%' (without the double-quote) then I don't get the error. I'm trying to understand why the double-quote causes the error and what it means in a CONTAINS search. Is a double-quote special in a CONTAINS search? Thanks, Kevin. On May 6, 12:35=A0am...

Oracle text search is slow. Need help
We have oracle 10g r2 running on RHEL4 iwth 4g ram We are using oracle text indexing feature to index LOB columns. But the query is taking more than a minute. Can someone please help me on this. Here is the code select * from arc_44 where FILE_DATE >= to_date('01/01/2001','dd-mm- yyyy') and FILE_DATE <= to_date('15/12/2001', 'dd-mm-yyyy') and ( contains(FULL_TEXT,'malaysia' ) > 0 or contains(CONTENT,'malaysia' ) > 0 or upper(META_DATA) LIKE upper('%malaysia%') ) ; Here FULL_TEXT and CONTENT are CLOB datatypes. ...

Search for text in a text field
Hi When posting a search box for the users, how do you take their input and search whether or not it is contained in a text field in many records and then diplay the records which contains the input. Thanks in advance, Luke. There is a function called InStr that will probably do what you require. Cheers, Chris "Luke Jones" <luke_jones_82@yahoo.co.uk> wrote in message news:24bb1508.0407080157.d587c63@posting.google.com... > Hi > > When posting a search box for the users, how do you take their input > and search whether or not it is contained in a text field in ...

how to: oracle full text search over multiple columns
Hello there I am looking for a way to realize a full text search on a whole table in oracle 8.1.7 . I found "catsearch()" and "contains()" features. But it seems that these only supports the lookup for a single column. So exists there way to realize a full text search over multiple columns ? How ? second Question I have is about, does Oracle supports an automatic translation of special characters suring the search ? for instance german "umlaute" so that the a umlaut (&auml;) is automatic trnsalted into 'ae' or 'a'. Or that the french charact...

insert values in text file to Oracle database
Hi I have values in a text file for example; A;100 B;200 C;300 D;400 .......... It is time consumming to type insert into table values ('A',100); insert into table values ('B',200); ..... Is there any mechanism in Oracle to insert automatically from a text file? Thanks Ngoc "ngoc" <linh@chello.no> wrote in message news:5HKNb.268$O41.874@amstwist00... > Hi > I have values in a text file for example; > A;100 > B;200 > C;300 > D;400 > ......... > It is time consumming to type > insert into table values ('...

Google API to make text search into database
Hi! I'd like to know if it's possible to use Google APIs to make a text search into DBs such Oracle or Sql Server. Thanks! Do not use SQL for text and document searching; get a tool built for this job. If you are doing this only once, then yhou can use whatever your RDBMS product has for strings. Are you trying to expose the content in your database out to the ouside world so its indexed via google? If so, you can create a 'site map' in google - search google for this, but you are in control of what is exposed and basically all you are doing is linkin...

Thesaurus management system for Oracle Text databases
Hi there, now I find a new administration tool for Oracle Text Thesauri, see: http://www.moving-objects.de/en/product/thema/index.html Now discard the old Oracle Text Manager from the Oracle Enterprise Manager and use the web based thesaurus manager Installation as J2EE Application for example in OC4J cu JR ...

How to write shell script to export oracle 8.1.6 database to import oracle 10g database
Hi, I am trying to write or understand the conj process for export the data from oracle 8i to oracle 10g database. I have constraint that the previous loaded data into oracle10g should not be deleted or replace by newly imported database. Regards Nikhil ...

US-VA: McLean-Oracle DBA - Oracle Database Administrator - RAC
************************************************************** JobCircle.com is the largest regional job board in the Mid-Atlantic region, with tens of thousands of job offerings in PA, NJ, DE, MD, NY, and Washington D.C. To learn more, visit http://www.jobcircle.com?source=ng ************************************************************** Job Title: Oracle DBA - Oracle Database Administrator - RAC - Oracle Job Location: VA: McLean Pay Rate: competitive Job Length: full time Start Date: 2010-11-19 Company Name: Cybercoders Contact: Shea Stone Phone: ...

Oracle Text Search Across Multiple Columns and Different Tables
Hi, I'm working on a general site seach which needs to search up to about 20 unrelated tables. I've managed to get a basic version of this working using Oracle Text (10g) with a materialized view, but I'm not sure how this will perform when the table sizes grow (say in the region of 100s of 1000s of records). Do anyone know if performance will be an issue using a materialized view for Oracle Text search? Is it efficient to use a materialized view to concatenate the data from, say, around 20 tables? Is that any other methods that can be used to index multiple columns on multiple t...

Web resources about - Text search - comp.databases.oracle.misc

SEO, Search Engine Optimisation - Website Optimisation
Hit Search Limited - digital marketing agency specialising in search engine optimisation, affiliates, ROI and pay per click. Online marketing ...

SEO & Search Engine News : SEJ
Latest Search Engine News from the Search Engine Journal.

Search Engine Marketing (SEM), Paid Search Advertising (PPC) & Search Engine Optimization (SEO) - Search ...
The authoritative guide to search engine marketing (SEM), search engine optimization (SEO), paid search advertising (PPC) and social media marketing. ...

Search Newz :: Search Engine News
... To Fight Over Paid Links Google prepared a note about a proposal by French lawmakers and backed by French news publsihers, which want search ...

State of Search - Where Search and Social meet up
The fastest growing Search and Social blog covering strategy developments in search and social media

Google Search Appliance - Wikipedia, the free encyclopedia
its search capabilities include searching web content, other file types (e.g. html, pdf, office documents), databases ( Oracle , MySQL , Microsoft ...

Fatal stabbing at Gilles Plains - AdelaideNow Search Search
A MAN has been stabbed to death at a home in Adelaide’s northeast, sparking a major police manhunt for the killer.

2024 Olympics: Four cities open campaign to host big sporting event - The Courier-Mail Search Search
CANDIDATURE files from Paris, Rome, Los Angeles and Budapest to host the 2024 Olympic Games were all delivered on schedule, the International ...

Dubbo man killed when ‘home made device’ exploded - DailyTelegraph Search Search
A MAN who died after a suspected homemade explosive blew up in his hand in western NSW has been remembered as sweet and passionate.

Rare stealth military-style bikes stolen in sneak attack on southeast manufacturer - HeraldSun Search ...
FIVE rare and expensive electric bikes which are usually sold to military agencies were stolen from a Springvale business on February 9.

Resources last updated: 2/20/2016 1:27:59 AM