Oracle SQL to shorten string in oracle

Question

I am trying to automate API load and facing field restriction. Basically I need to shorten string if its more than 24 characters.

I am considering logic to take the first 4 characters of the word and replace space with _. A number of words in the field are dynamic.

e.g. Corporate Responsibility = copr_resp

E.g. Social Distancing Criteria = soci_dist_crit

Table_a

ColA Corporate Responsibility Social Distancing Criteria

Expected Result:

Select ColA, as Output from table_a;

Output ColA Output Corporate Responsibility copr_resp Social Distancing Criteria soci_dist_crit

I prefer to do this using SQL. Any suggestion?

what is the max length of the expected output strings if you have a field restriction? — Slkrasnodar
– Slkrasnodar, Commented Jun 26, 2020 at 15:07

Popeye · Accepted Answer · 2020-06-26 14:52:09Z

2

You can divide the string into row and concat again as follows:

Select t.your_col,
       listagg( substr(regexp_substr(t.your_col,
                                    '[^ ]+', 1, levels.column_value), 
                       1, 4), 
              '_') within group (order by levels.column_value) as short_str
from your_table t
Cross join table(cast(multiset(select level from dual 
                 connect by level <= length (regexp_replace(t.your_col, '[^ ]+'))  + 1) 
                  as sys.OdciNumberList)) levels
Group by t.your_col

edited Jun 26, 2020 at 14:52

answered Jun 26, 2020 at 14:46

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

np4coding Over a year ago

almost there. I know I didnt mention it in original question. But is it possible to remove special character like & and - completely? Following gives output of : Lice_&_Lega_Resp, it will be nice to have lice_lega_resp

np4coding Over a year ago

Select t.sub_division, listagg( substr(regexp_substr(t.sub_division, '[^ ]+', 1, levels.column_value), 1, 4), '_') within group (order by levels.column_value) as short_str from (SELECT 'Licensing & Legal Responsibility' sub_division FROM dual) t Cross join table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.sub_division, '[^ ]+')) + 1) as sys.OdciNumberList)) levels Group by t.sub_division;

Collectives™ on Stack Overflow

Oracle SQL to shorten string in oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related