SQL return substrings

[Grazia Lassner]

Does anyone know how to
select the first substring before a given character with SQL ?
For instance, if my column ID contains data like ABC_XYZ or BC_Z, how could I select the 'ABC' or 'BC' occurring before the '_' ?

Thanks !

[Martin Vanyavchich]

Given that your ABC_XYZ is a String, and you're looking for a Java solution ...

  public static void main(String[] args){
    String wholeString = "ABC_XYZ";
    
    String[] choppedSubstrings = wholeString.split("_"); // elements of this array are now: ["ABC", "XYZ"]

    System.out.println(choppedSubstrings[0]); // prints ABC
  }

[Grazia Lassner]

Sorry, I meant a way with a SQL select statement

[Martin Vanyavchich]

Well I think that greatly depends on the database that you're using. So this probably isn't the right forum for your question. Try finding the forum for the database you're using.

[Grazia Lassner]

I was trying to find an Oracle forum here, but I guess it does not exist

[Tom Reilly]

Try instr function

[Peter Taucher]

On MS SQL Server:

SELECT SUBSTRING(Name,0,CHARINDEX('_',Name)), SUBSTRING(Name,CHARINDEX('_',Name),LEN(Name)) [...]

On PostgreSQL:

SELECT SUBSTRING(Name,0,STRPOS(Name,'_')), SUBSTRING(Name,STRPOS(Name,'_'),CHAR_LENGTH(Name)+1) [...]

String functions seem to differ largely between vendors...

[Martin Vanyavchich]

Link to Oracle forum, try in the 'SQL and PL/SQL' section

[Grazia Lassner]

select distinct Substr(column,1,instr(column,'_')) from ....