Search code examples
postgresqlpostgresql-9.3

PostgreSQL 9.3: STUFF and CHARINDEX function

I want to retrieve some part of given string.

Here is the following example for the string:

Example: In SQL Server 

Declare @Names varchar = 'H1,H2,H3,'

SELECT STUFF(@Names,1,CHARINDEX(',',@Names,0),'');

After referring this : 'stuff' and 'for xml path('')' from SQL Server in Postgresql.

String_agg can't help me for this scenario.

Solution

  • You are looking for equivalent TSQL function of STUFF in PostgrSQL which i think is: overlay

    so for example:

    SELECT STUFF('abcdef', 2, 3, 'ijklmn');

    and 

    select overlay('abcdef' placing 'ijklmn' from 2 for 3)

    give both the same result which is;

    'aijklmnef'

    in other words:

    They inserts a string into another string. It deletes a specified length of characters in the 
first string at the start position and then inserts the second string into the first string at 
the start position.