how to get 8 characters in a string after joining two words in oracle sql

user_13865519 0
Viewed 80

i need to get first 8 characters after replacing whitespaces of a two string example "united states of america" by find and replace removed whitespaces "unitedstatesofamerica" but i need string upto length of 8 after removing whitespaces ie "unitedst" pls help me

oracle sql
3 Answers

I think you just want substr():

select substr(replace(col, ' ', ''), 1, 8)
user_11440350

One option would be using [[:space:]] POSIX within REGEXP_REPLACE() function in order to remove all whitespace characters including non-printable ones such as

SELECT SUBSTR( REGEXP_REPLACE(col,'[[:space:]]'), 1, 8) AS col
  FROM t -- your original table

Demo

user_58413060

If you need to remove all whitespaces (spaces, tabs, line breaks) then you want to use a regular expression to replace the whitespaces and then you can use SUBSTR to find the first 8 characters:

SELECT SUBSTR(REGEXP_REPLACE(col, '\s+'), 1, 8)
FROM   table_name

Which, for the test data:

CREATE TABLE table_name ( col ) AS
SELECT 'United States of America' FROM DUAL UNION ALL
SELECT 'ABCDEFGHIJKLM' FROM DUAL UNION ALL
SELECT 'A B' || CHR(9) || 'C' || CHR(13) || CHR(10) || 'D E F G H I J K L M' FROM DUAL;

Outputs:

| SUBSTR(REGEXP_REPLACE(COL,'\S+'),1,8) |
| :------------------------------------ |
| UnitedSt                              |
| ABCDEFGH                              |
| ABCDEFGH                              |

db<>fiddle here

user_15092640
Related