Removing specified character from string in SQL

Question

Say I have a string looking like this ",LI,PA,LK"; I want to remove the first char, so it looks like "LI,PA,LK";

In Java my code to handle this, will look like this:

public String returnSubs(String val) {

    int index = val.indexOf(",");       
    String res = val.substring(index+1, val.length());

    return res;
}

I want to achieve the exact same thing in SQL, having this query

    select patientID, case when liver is not null then 'LI' else '' end 
         || case when kidney_r is not null then ',KR' else '' end
         || case when kidney_l is not null then ',KL' else ''end
         || case when heart is not null then ',HE' else '' end
         || case when liver_domino is not null then ',LI-Dom' else '' end
         || case when lung_r is not null then ',LungR' else '' end
         || case when pancreas is not null then ',PA' else '' end
         || case when liver_split is not null then ',Lsplit' else '' end
         || case when lung_l is not null then ',LungL' else '' end
         || case when intestine is not null then ',Intestine' else '' end
         into organType                       
from offers
where patientID > 1
;

Also, the string I get from the query above, could look like LI, PA, KL, (notice the comma is at the end, and not the begining)

I see that I can use the SUBSTRING and/or INSTR of SQL. But I'm not really sure how. I am creating a procedure where this will be handled

Thanks for any help

Gordon Linoff · Accepted Answer · 2019-11-27 13:30:57Z

2

Oracle has a function trim() that does exactly what you want:

trim(leading ',' from col)

You can use this in either an update or select.

Note: You appear to be storing multiple values in a comma-delimited list. That is a very bad way to model data. You do not want to overload what strings are by storing multiple values. Oracle has many better alternatives -- association tables, nested tables, JSON, and XML come to mind.

answered Nov 27, 2019 at 13:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2019-11-27 13:32:06Z

1

You could also use LTRIM here:

SELECT
    LTRIM(organTypes, ',') AS col_out
FROM offers;

Some databases, such as MySQL, offer functions like CONCAT_WS which concatenate with a separator while ensuring that no dangling separators are added to the resulting output. Oracle does not have this, but LTRIM should be sufficient here.

answered Nov 27, 2019 at 13:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Nikhil · Accepted Answer · 2019-11-27 13:38:04Z

1

even this will work:

 substr(',LI,PA,LK',2)

answered Nov 27, 2019 at 13:38

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

Comments

Milo · Accepted Answer · 2019-11-27 18:50:52Z

0

In SQL SERVER:

SUBSTRING(VAL,2,LEN(VAL))

VAL--> COLUMN NAME
2--> IT SKIPS 1ST VALUE
LEN-->LENGTH OF THE COLUMN

edited Nov 27, 2019 at 18:50

Milo

3,4539 gold badges32 silver badges44 bronze badges

answered Nov 27, 2019 at 13:44

saravanatn

6305 silver badges9 bronze badges

Collectives™ on Stack Overflow

Removing specified character from string in SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related