Remove extra space , number, characters from start and end of the string-CodePudding

Need to remove extra space , number, characters from start and end of the string

Column 1
11 2013US83838
2019IN353535 2


Output
2013US83838
2019IN353535

CodePudding user response：

If your desired format has 4 digits (year?) then 2 upper-case letters (country code?) and then a 5-or-more digit number then you can use:

SELECT REGEXP_REPLACE(
         column1,
         '^(.* )?(\d{4}[A-Z]{2}\d{5,})( .*)?$',
         '\2'
       ) AS output
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (Column1) AS
SELECT '11 2013US83838' FROM DUAL UNION ALL
SELECT '2019IN353535 2' FROM DUAL;

Outputs:

OUTPUT
2013US83838
2019IN353535

fiddle

CodePudding user response：

This will return the longest substring from the column. I have no idea whether that's what you want or not because discussion (in comments) didn't help much.

Sample data:

SQL> with test (col) as
  2    (select '11 2013US83838' from dual union all
  3     select '2019IN353535 2' from dual
  4    ),

Split values into rows, separated by space character. Rank substrings by their length:

  5  temp as
  6    (select col,
  7       regexp_substr(col, '[^ ] ', 1, column_value) val,
  8       length(regexp_substr(col, '[^ ] ', 1, column_value)) len,
  9       rank() over (partition by col order by length(regexp_substr(col, '[^ ] ', 1, column_value)) desc) rnk
 10     from test cross join
 11       table(cast(multiset(select level from dual
 12                           connect by level <= regexp_count(col, ' ')   1
 13                          ) as sys.odcinumberlist))
 14    )

Finally, fetch values that ranked as the highest (val is desired output):

 15  select col, val
 16  from temp
 17  where rnk = 1;

COL            VAL
-------------- --------------
11 2013US83838 2013US83838
2019IN353535 2 2019IN353535

SQL>