CODEWITHSUNDEEP
sqloracle-database
np4coding
np4coding

Reputation: 297

Oracle SQL to shorten string in oracle

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?

Upvotes: 0

Views: 220

Answers (1)

Popeye
Popeye

Reputation: 35900

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

Upvotes: 2

Related Questions