1

Hello, So here is what I am trying to do. I am selecting a column say ““DEPT”” from a table. Now instead of chars, i need to retun 1 for every upper case char and 0 for every lower case char. For example if DEPT = ‘FINancE’ then return 1110001. I know this is a crazy requirement but I am working on an exporter tool and this is what the I need to accomplish through SQL. Can someone help me with this please? Thanks a lot. Rahul

2

Thanks a lot All.
Translate function works perfectly for me.
I tried using REGEXP also but it looks like a lot of work.
Thanks again
Rahul

3

The REGEXP_REPLACE will accomplish, However, you need to invoke the function twice (below). The problem is that a regex is -well- regular and thus predictable. In this case we are dealing with - for lack of a better term - a ‘random expression’ in that there is no predictability to the sequence of upper and lower case letters.

Now you could still argue it’s still more elegant. But, on my system at least it was consistently about 40% slower.
The following each produced the same result with the TRANSLATE being faster.

with parm as (select ‘FINancE’ f from dual) select regexp_replace( regexp_replace( f, ‘[A-Z]’ , ‘1’ ), ‘[a-z]’,‘0’) from parm ;

with parm as (select ‘FINancE’ f from dual)select translate( f, ‘QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm’
, ‘1111111111111111111111111100000000000000000000000000’)
from parm ;

Brlayer

4

Rahul,
Try this:
select translate (‘FINancE’,
‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz’,
‘1111111111111111111111111100000000000000000000000000’) from dual
Thx
Randhir

5

I wonder if anybody has regex that would do the same. I am stumped by the replacement. Obviously translate works but it would seem that regex would be more elegant. Any help?

6

You can use TRANSLATE. To put it very plainly:
SELECT TRANSLATE
(
TRANSLATE(dept
,‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’
,‘11111111111111111111111111’
)
,‘abcdefghijklmnopqrstuvwxyz’
,‘00000000000000000000000000’
)
FROM DEPARTMENT
;
You might get more fancy, but that’s not what you asked…
-Bill Wire
rahul chandrawanshi via oracle-dev-l wrote:

Hello,

So here is what I am trying to do.
I am selecting a column say “DEPT” from a table.
Now instead of chars, i need to retun 1 for every upper case char and
0 for every lower case char.
For example if DEPT = ‘FINancE’ then return 1110001.
I know this is a crazy requirement but I am working on an exporter
tool and this is what the I need to accomplish through SQL.
Can someone help me with this please?
Thanks a lot.
Rahul

7

try this…

select ‘FINancE’ mystr,
translate(‘FINancE’,
‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz’,
‘1111111111111111111111111100000000000000000000000000’) newstr
from dual