If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Converting String to Binary

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,497
Will ASCII(char) work for you?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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

Quote:
Originally posted by anacedent
Will ASCII(char) work for you?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,497
Quote:
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?
Reply With Quote
  #5 (permalink)  
Old
Drunkard
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Drunkard
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2002
Posts: 1,245
Wow! Thanks for your help.

Regards,

hmscott
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On