Convert text to lower case except the strings within single quotes

Shell Programming and Scripting
1

Shell : bash that comes with RHEL 6.7

I have SQL scripts like below. I want to convert all the text in these files to lower case except the strings enclosed within single quotes .
Any idea how I can achieve this ?

Sample text:

$ cat sample.txt
SELECT  A.LOGIN_ID,A.password,NVL(A.TEMP_PASS_EXPIRY_DT,SYSTIMESTAMP),NVL(UPPER(A.STATUS), ' ')
  INTO V_LOGIN_ID, V_PASSWORD , V_TEMP_PASS_EXPIRY_DT,V_STATUS
  FROM W_DEALER_MASTER A, W_DEALER_USER_MAP B
  WHERE A.LOGIN_ID = I_LOGINID AND B.LOGIN_ID=A.LOGIN_ID AND B.ROLE_ID in ('sysAdmSupervisorR2','sysAdmSalesRepR2');

        IF V_PASSWORD = v_entered_password AND V_STATUS = 'ACTIVE' THEN
                        O_STATUS := 'SUCCESS';
                        O_MESSAGE := null;
                ELSE
                    O_STATUS := 'FAIL';

Expected output:

select  a.login_id,a.password,nvl(a.temp_pass_expiry_dt,systimestamp),nvl(upper(a.status), ' ')
  into v_login_id, v_password , v_temp_pass_expiry_dt,v_status
  from w_dealer_master a, w_dealer_user_map b
  where a.login_id = i_loginid and b.login_id=a.login_id and b.role_id in ('sysAdmSupervisorR2','sysAdmSalesRepR2');
    
    if v_password = v_entered_password and v_status = 'ACTIVE' then 
            o_status := 'SUCCESS';
            o_message := null;
        else 
            o_status := 'FAIL';
2

Any attempts / ideas / thoughts from your side?

3

Hi, John K,
you may try this:

awk '{ 
           for (i=1; i<=NF; i++ )
                   if ( substr($i, 1, 1) == "\x27" && substr($i, length($i), 1)=="\x27") 
                           printf("%s ", $i);
                   else printf("%s ", tolower($i));
                  
                   printf("\n");

        }' sample.txt

Greetings!

1 Like
4

Hi, AbelLuis.

Unless there is something additional here (or that I missed), this appears to fail if the quoted string is not bounded by whitespace ... cheers, drl

Results:

select a.login_id,a.password,nvl(a.temp_pass_expiry_dt,systimestamp),nvl(upper(a.status), ' ') 
into v_login_id, v_password , v_temp_pass_expiry_dt,v_status 
from w_dealer_master a, w_dealer_user_map b 
where a.login_id = i_loginid and b.login_id=a.login_id and b.role_id in ('sysadmsupervisorr2','sysadmsalesrepr2'); 

if v_password = v_entered_password and v_status = 'ACTIVE' then 
o_status := 'success'; 
o_message := null; 
else 
o_status := 'fail';

On a system like:

OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.7 (jessie) 
awk GNU Awk 4.1.1, API: 1.1 (GNU MPFR 3.1.2-p3, GNU MP 6.0.0)
2 Likes
5

Hi, drl,
this worked on the original sample:

#!/usr/bin/awk -f

{
n1=match($0, "\x27"); 
n2=match(substr($0, n1+1), "\x27"); 

while ( n1 > 0 ){
    printf("%s", tolower(substr($0, 1, n1-1)));
    printf("%s", substr($0, n1, n2+1));
    $0=substr($0, n1+n2+1);
    n1=match($0, "\x27"); 
    n2=match(substr($0, n1+1), "\x27"); 
}
    print tolower($0);

}

The output was:

select  a.login_id,a.password,nvl(a.temp_pass_expiry_dt,systimestamp),nvl(upper(a.status),
' ')
into v_login_id, v_password , v_temp_pass_expiry_dt,v_status
from w_dealer_master a, w_dealer_user_map b
where a.login_id = i_loginid and b.login_id=a.login_id and b.role_id
in ('sysAdmSupervisorR2','sysAdmSalesRepR2');
if v_password = v_entered_password and v_status = 'ACTIVE' then
o_status := 'SUCCESS';
o_message := null;
else
o_status := 'FAIL';

Greetings!

1 Like
6

Try

awk -F"'" -vOFS="'" '{for (i=1; i<=NF; i+=2) $i = tolower($i)} 1 ' file
select  a.login_id,a.password,nvl(a.temp_pass_expiry_dt,systimestamp),nvl(upper(a.status), ' ')
  into v_login_id, v_password , v_temp_pass_expiry_dt,v_status
  from w_dealer_master a, w_dealer_user_map b
  where a.login_id = i_loginid and b.login_id=a.login_id and b.role_id in ('sysAdmSupervisorR2','sysAdmSalesRepR2');

        if v_password = v_entered_password and v_status = 'ACTIVE' then
                        o_status := 'SUCCESS';
                        o_message := null;
                else
                    o_status := 'FAIL';
2 Likes
7

Try: (GNU sed):

sed -r "s/([^']*)(('[^']*')?)/\L\1\E\2/g"  file
2 Likes