Converting String to Binary

hmscott · #1 (**permalink**) 12-17-03, 13:39

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

Thanks,

hmscott

anacedent · #2 (**permalink**) 12-17-03, 14:46

Will ASCII(char) work for you?

hmscott · #3 (**permalink**) 12-17-03, 15:55

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?

anacedent · #4 (**permalink**) 12-17-03, 17:22

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?

billm · #5 (**permalink**) 12-17-03, 20:50

Hi,

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

Hth
Bill

hmscott · #6 (**permalink**) 12-18-03, 10:23

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

billm · #7 (**permalink**) 12-18-03, 11:23

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_ascii( inSource IN VARCHAR2 ) AS

   ResultString varchar2(381); -- 3 * possible length of source

begin

   for i in 1..length( inSource ) loop

       ResultString := ResultString || trim( to_char(ascii( substr( inSource,i,1 ) ),'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_ascii( column name )

from table

Hth
Bill

hmscott · #8 (**permalink**) 12-18-03, 13:08

Wow! Thanks for your help.

Regards,

hmscott

Internet Services

Web Development

Digital Marketing

Consumer Tech