Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Converting String to Binary

    Is there a function in PL/SQL to convert a string to binary? What would it be?

    Thanks,

    hmscott

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    Will ASCII(char) work for you?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Will that work on multi-character strings. Sorry, I guess I could have been a bit more explicit.

    I'm extracting data from a source table in Oracle to a SQL Server table. Oracle is set as Case-Sensitive, while SQL was set up to be Case-Insensitive. In order to populate a table and retain the correct PK (from Oracle), I have to convert the column from string to binary (I keep a copy of the original string in a separate column).

    Thus I need to have 50145J in binary and 50145j in binary as different values.

    I think ASCII(char) will only work on a single character. Am I wrong?

    Regards,

    hmscott

    Originally posted by anacedent
    Will ASCII(char) work for you?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,621
    Originally posted by hmscott
    Will that work on multi-character strings. Sorry, I guess I could have been a bit more explicit.

    I'm extracting data from a source table in Oracle to a SQL Server table. Oracle is set as Case-Sensitive, while SQL was set up to be Case-Insensitive. In order to populate a table and retain the correct PK (from Oracle), I have to convert the column from string to binary (I keep a copy of the original string in a separate column).

    Thus I need to have 50145J in binary and 50145j in binary as different values.

    I think ASCII(char) will only work on a single character. Am I wrong?

    Regards,

    hmscott
    ASCII only works with a single character at a time,
    but there is nothing to prevent you from invoking ASCII
    within a loop to append together results returned by it.

    You need to separate what is typically display for human readability,
    with actual internal data storage values.


    You have now made things less clear (at least to me).
    "Binary" does not compute in this case to my view of reality.
    "Binary number" is a STRING ( data type character) of ONES & ZEROES
    Somehow I don't think this is what you want/plan to construct.
    Keep in mind that when the character string "50145J" is really & truly
    converted to BINARY it becomes a STRING that is 48 characters long!

    Rhetorical question-
    If both 50145J AND 50145j are primary keys within a single Oracle table of datatype VARCHAR2, what will be the datatype of the PK in SQL-Server and how will the two values be different?

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Wouldn't upper( pk_column ) achieve the same results for you?

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    I'm sorry, I should have included the DDL. The table in Oracle is described as:

    Code:
    PALLET_ID   VARCHAR2(127)  NOT NULL  (PK)
    ....
    More Columns
    The table in SQL is described as:

    Code:
    PALLET_ID        NVARCHAR(127) NOT NULL
    ...
    More columns
    ...
    PK_PALLET_ID  VARBINARY(127) NOT NULL (PK)
    In Oracle (since the dictionary sort is case sensitive), 50145J and 50145j are different and therefore allowable as separate rows.

    In SQL (since the dictionary sort is case INsensitive), 50145J and 50145j are treated as identical values and therefore there is a primary key violation. But I figured if I added a new column (PK_PALLET_ID varbinary(127)) and put a binary representation of the PALLET_ID in there, I could maintain a primary key. The users would not be seeing the data in this column, they would only see the data in the PALLET_ID column.

    In SQL, I can use CAST(column_name as varbinary) to convert a string into it's binary representation. I was hoping that Oracle had a simlar function.

    For now, as a temporary measure, I am importing the raw data into a temporary table in SQL, then moving it into the permanent table (and thus I get to use the T-SQL CAST function).

    Sorry for the confusion,

    hmscott

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I don't know of any Oracle functions to do this, maybe this can help instead. This function will return it as an ASCII string (somewhat shorter than a binary representation).

    PHP Code:
    create or replace function to_asciiinSource IN VARCHAR2 ) AS
       
    ResultString varchar2(381); -- possible length of source
    begin
       
    for i in 1..lengthinSource loop
           ResultString 
    := ResultString || trimto_char(asciisubstrinSource,i,) ),'009')); 
       
    end loop;
       return 
    ResultString;
    end
    Ideally you would put it in a package and add a RESTRICT_REFERENCES to aid Oracle performance wise.

    PHP Code:
    select to_asciicolumn name )
    from table 
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Wow! Thanks for your help.

    Regards,

    hmscott

Posting Permissions

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