Partially replacing strings in SQL

Question

I wonder if I can do the following directly in SQL. I have a text-formatted column with values separated by -. So an example row value could look 1-42-9. I want to make a selection such that each string in between the - is replaced by another according to some logic. As an example, let's say my logic says that each string equal to 42 should be replaced by ABC which would give me 1-ABC-9. Can this be done in a SELECT statement?

Answer 1

If the column contains a value like '1-42-9' and you want to search for '42' to replace with 'ABC', then you must take in account all cases like:

• '42' is at the beginning of the column
• '42' is in the middle of the column
• '42' is at the end of the column
• '42' is the only value of the column

These 4 cases can be handled with a more complicated but precise method:

select substr(
  replace('-' || col || '-', '-42-', '-ABC-'), 
  2,
  length(replace('-' || col || '-', '-42-', '-ABC-')) - 2
) NewCol
from tablename;

See the demo.
For these values:

create table tablename (col TEXT);
insert into tablename (col) values
('1-42-9'),
('42-1-9'),
('9-1-42'),
('42'),
('1-100-9');

The results are:

| NewCol  |
| ------- |
| 1-ABC-9 |
| ABC-1-9 |
| 9-1-ABC |
| ABC     |
| 1-100-9 |

Answer 2

Using substring and case, you can do something like the following:

select case when '1-42-9' like '%-42-%'
then replace('1-42-9','-42-', '-ABC-' )
else '1-42-9' end 

Instead of the static value of '1-42-9', you need to write the column name.

Answer 3

Can this be done in a SELECT statement?

Yes, you can use the replace function

To do just do the 1 replace you could use :-

SELECT replace(mycolumn,'-42-','ABC') AS mycolumn FROM mytable;

A working example that demonstrates this simple singular replacement is :-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mycolumn);
INSERT INTO mytable VALUES ('1-42-9'),('1429'); -- add some data (one row to be changed, the other not to be changed)
SELECT replace(mycolumn,'-42-','ABC') AS mycolumn FROM mytable; -- Do the replace maintaing the column name

This results in :-

If you wanted a more complex replacement say replacing two items you could use nested replaces , noting that this can be quite tedious as the replacement order is relevant for example to replace -9 with XYZ and -42- with ABC so that 1-42-9 become 1ABCXYZ then you could use :-

SELECT replace(replace(mycolumn,'-9','-XYZ'),'-42-','ABC') AS mycolumn FROM mytable;

If you wanted multiple replacements of which only 1 would be done say -42- is replaced with ABC or -43- with DEF or -44- with GHI then you could use a CASE WHEN THEN END construct along the lines of :-

SELECT
    CASE 
      WHEN instr(mycolumn,'-42-') THEN replace(mycolumn,'-42-','ABC')
      WHEN instr(mycolumn,'-43-') THEN replace(mycolumn,'-43-','DEF')
      WHEN instr(mycolumn,'-44-') THEN replace(mycolumn,'-44-','GHI')
      ELSE mycolumn
  END AS mycolumn
FROM mytable;

Answer 4

The dynamic solution that is SQLite compatible would be to nest substr and instr functions to split the list into elements and then apply replacement logic to elements.

For a table strings that has columns id with values '1-42-9', '777-5-21' and '7-55-123' (so you're sure it works with variable lengths of the elements and covers 1 vs 111 case), it would be:

SELECT
 input 
,first_element || '-' ||
 CASE second_element 
    WHEN '42' THEN 'ABC'
    WHEN '5' THEN 'DEF'
    ELSE second_element
 END || '-' ||
 third_element as output
FROM (
    select 
    id as input,
    substr(
      id,
      1,
      instr(id,'-')-1
    ) as first_element,             
    substr(
      substr(id,instr(id,'-')+1,100),
      1,
      instr(substr(id,instr(id,'-')+1,100),'-')-1
    ) as second_element
    ,substr(
      substr(id,instr(id,'-')+1,100),
      instr(substr(id,instr(id,'-')+1,100),'-')+1,
      100
    ) as third_element
    from strings
) t       

fiddle

(might be more beautiful but it's working:))

this works only for string lists that have 3 elements separated by dashes

if you want to replace a certain element at any position just apply the same CASE statement to first_element and third_element

also, you can do simply this:

replace('-'||id||'-','-42-','-ABC-') - if you wrap your strings into an additional pair of dashes you would be able to search for 42 regardless of its position (start, middle, end)