How to Select a substring in Oracle SQL up to a specific character

Question

Say I have a table column that has results like   ABC blahblahblah DEFGH moreblahblahblah IJKLMNOP moremoremoremore   I would like to be able to write a query that selects this column from said table  but only returns the substring up to the Underscore     character  For example   ABC DEFGH IJKLMNOP   The SUBSTRING function doesn t seem to be up to the task because it is position-based and the position of the underscore varies   I thought about the TRIM function  the RTRIM function specifically    SELECT RTRIM  listofchars  FROM somecolumn   FROM sometable   But I m not sure how I d get this to work since it only seems to remove a certain list set of characters and I m really only after the characters leading up to the Underscore character

User · Answer

In case if String position is not fixed then by below Select statement we can get the expected output.

Table Structure ID VARCHAR2(100 BYTE) CLIENT VARCHAR2(4000 BYTE)

Data- ID CLIENT
1001 {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"}
1002 {"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"}

Requirement - Search "ClientId" string in CLIENT column and return the corresponding value. Like From "clientId":"con-bjp" --> con-bjp(Expected output)

select CLIENT,substr(substr(CLIENT,instr(CLIENT,'"clientId":"')+length('"clientId":"')),1,instr(substr(CLIENT,instr(CLIENT,'"clientId":"')+length('"clientId":"')),'"',1 )-1) cut_str from TEST_SC;

CLIENT cut_str ----------------------------------------------------------- ---------- {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"} con-bjp {"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"} Test-Cust

User · Answer

This can be done using REGEXP SUBSTR easily   Please use   REGEXP SUBSTR  STRING EXAMPLE          1 1     where STRING EXAMPLE is your string   Try   SELECT  REGEXP SUBSTR  STRING EXAMPLE          1 1   from dual   It will solve your problem

User · Answer

Remember this if all your Strings in the column do not have an underscore      or else if null value will be the output    SELECT COALESCE  SUBSTR  STRING COLUMN    0  INSTR  STRING COLUMN       -1     STRING COLUMN    AS OUTPUT FROM DUAL

User · Answer

To find any sub-string from large string   string value    This is String Please search string  Ple      Then to find the string  Ple  from String value we can do as   select substr string value instr string value  Ple   length  Ple    from dual    You will find result  Ple

User · Answer

SELECT REGEXP SUBSTR  STRING EXAMPLE          1 1   from dual   is the right answer  as posted by user1717270  If you use INSTR  it will give you the position for a string that assumes it contains     in it  What if it doesn t  Well the answer will be 0  Therefore  when you want to print the string  it will print a NULL  Example  If you want to remove the domain from a  host domain   In some cases you will only have the short name  i e   host   Most likely you would like to print  host   Well  with INSTR it will give you a NULL because it did not find any      i e  it will print from 0 to 0  With REGEXP SUBSTR you will get the right answer in all cases   SELECT REGEXP SUBSTR  HOST DOMAIN          1 1   from dual    HOST  and  SELECT REGEXP SUBSTR  HOST          1 1   from dual    HOST

User · Answer

Another possibility would be the use of REGEXP SUBSTR

User · Answer

You need to get the position of the first underscore  using INSTR  and then get the part of the string from 1st charecter to  pos-1  using substr     1  select  ABC blahblahblah  test string    2         instr  ABC blahblahblah      1 1  position underscore    3         substr  ABC blahblahblah  1 instr  ABC blahblahblah      1 1 -1  result   4    from dual SQL gt     TEST STRING      POSITION UNDERSCORE RES ---------------- ------------------  --- ABC blahblahblah                  4  ABC   Instr documentation  Susbtr Documentation

User · Answer

Using a combination of SUBSTR  INSTR  and NVL  for strings without an underscore  will return what you want   SELECT NVL SUBSTR  ABC blah   0  INSTR  ABC blah       -1    ABC blah   AS output   FROM DUAL   Result   output ------ ABC   Use   SELECT NVL SUBSTR t column  0  INSTR t column      -1   t column  AS output   FROM YOUR TABLE t   Reference    SUBSTR INSTR   Addendum  If using Oracle10g   you can use regex via REGEXP SUBSTR

[sql] How to Select a substring in Oracle SQL up to a specific character?

Examples related to sql

Examples related to oracle

Examples related to substring

Examples related to trim