PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Add prefix to beginning of string in postgres

I have a table in a postgres database with data that looks like this:
XX-001111111-11-Q011111o_ABC_90001u-0000101-9

The table has 1000 rows with similar looking strings.

I want to update every row to add the ‘AB-‘ prefix to it.

So XX-001111111-11-Q011111o_ABC_90001u-0000101-9 would look like AB-XX-001111111-11-Q011111o_ABC_90001u-0000101-9

I assume I can do this with regex_replace but everything I’ve tried has not worked.

Tags:

3

Answers


  1. 0

    Assume:

    • your table Name is Test1
    • your column name is sname

    SQL:

    UPDATE Test1
SET sname = 'AB-' || sname;

    I tested it in my environment and it can be executed successfully.

    PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

    Login or Signup to reply.
  2. 0

    You don’t need a regex operation to change the value (prefix a string) of a column. An example using psql shell:

    -- Create a table and insert a row with a string column
CREATE TABLE t1 (
  my_string VARCHAR(100) NOT NULL
);

INSERT INTO t1 VALUES ('XX-001111111-11-Q011111o_ABC_90001u-0000101-9');

-- Query the table
SELECT * FROM t1;
                   my_string
-----------------------------------------------
 XX-001111111-11-Q011111o_ABC_90001u-0000101-9

-- Update the table column value
UPDATE t1 SET my_string = concat('AB-', my_string);

-- Query the table to see the updated column value
SELECT * FROM t1;
                   my_string
-------------------------------------------------
 AB-XX-001111111-11-Q011111o_ABC_90001u-0000101-9

    The concat is a PostgreSQL string function. You can also use the || (concatenation operator) to produce the same result:

    UPDATE t1 SET my_string = 'AB-' || my_string;

    Refrerence: PostgreSQL String Functions and Operators

    Login or Signup to reply.
  3. 0

    You can use CONCAT which enhances readability and clearly conveys the intent of the operation:

    here you go , an example:

    UPDATE your_table_name
SET your_column_name = CONCAT('AB-', your_column_name);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search