Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Unanswered: replacing numbers in a string

    Hi

    I'm trying to build a function to look up values in a tables based on input provided to the function. In general the function takes the input and executes a select statement returning the column I would like to retrieve. The issue is that sometimes the input sometimes has numbers and the column we use to compare with the input does not have numbers. Also I am using Oracle 8i to do this.

    Is there a way to replace the numbers in the input with an asterisk (*) in Oracle 8i?

    I'll attach the basic form of my function.

    create or replace function file_lookup_hally(v_column1 varchar2)
    return varchar2 is

    return_val varchar2;

    begin

    select distinct column2
    into return_val
    from table1
    where column1 like v_column1
    return return_val;

    end;

    Thanks

    Hally

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a way to replace the numbers in the input with an asterisk (*) in Oracle 8i?
    use REPLACE() function
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Thanks, I was actually thinking about using this function but the issue is that I do not know which numbers may be in the input or even how many numbers are in the input.

    For example the values I will compare against in the table may be:
    abc.txt
    def.dat
    ghi.log

    but the input may look like the following examples:
    abc123.txt
    def.dat456
    7890ghi.log

    replace function will work if I know what the numbers are and how long the length of the numbers is. If someone could provide me a way of fetching all numbers in the string into function(no matter how the format looks like), I can then use the function.

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I do not know which numbers may be in the input
    Yes, you do know. Numbers will be zero to nine; inclusive
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    select distinct column_1
    from my_table
    where column_1 = translate ('123SMI.456TH', 'ABCDEFGHIJKLMNOPQRSTUVWXY0123456789.',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

    ------------------------------
    SMITH

    1 row selected.

  6. #6
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    this is the syntax for replace

    replace( string1, string_to_replace, [ replacement_string ] )

    I need to provide the parameter string_to_replace with the pattern i'm looking to replace, in this scenario I am looking for all numbers in the string. Lets take the first string abc123.txt. The only way I can replace the numbers is by issuing the following: replace('abc123.txt','123','%'). The problem is that I do not know what numbers the input will be so if I were to execute the following they would fail:

    replace('abc123.txt','1234','%')
    replace('abc123.txt','213','%')
    replace('abc123.txt','1234567890','%')

    my question is how can I use this function without knowing the number pattern?

  7. #7
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Quote Originally Posted by Ida Hoe View Post
    select distinct column_1
    from my_table
    where column_1 = translate ('123SMI.456TH', 'ABCDEFGHIJKLMNOPQRSTUVWXY0123456789.',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

    ------------------------------
    SMITH

    1 row selected.
    Thanks, this solution works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •