CHARINDEX () & PATINDEX () equivalent in PostgreSQL

MAV13
October 20, 2022
377 views
0 votes
2 Answers

I am trying to translate a T-SQL Query to PostgreSQL and one part of the query contains the string function CHARINDEX () which I am not able to translate to PgSQL.

Here is the part of the query with the string function:

SELECT 
                    REPLACE(REPLACE(SUBSTRING(
                        PARAMETRI
                        ,CHARINDEX('=',PARAMETRI, PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI) ) +1
                        ,CHARINDEX(
                            CHAR(13)||CHAR(10)
                            ,PARAMETRI
                            ,PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI)
                        ) 
                        -CHARINDEX('=',PARAMETRI, PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI) )
                    ),CHAR(13), ''), CHAR(10), '') AS SELECTED
                FROM dbo.XYZ

CHARINDEX(‘=’,PARAMETRI, PATINDEX(‘%ACCOUNTFILTERFST=%’, PARAMETRI)
‘=’ lokup this sign in column PARAMETRI and start at position whatever PATINDEX outputs as a result.

Edit: 21.10.2022

SELECT * FROM DBO.YYY
WHERE COD_SCHEMA IN (
SELECT 
    --TRIM(CHAR(13)+ CHAR(10)  + ' ' FROM  VALUE)
    RTRIM(LTRIM(value))
FROM 
    --STRING_SPLIT 
    fn_split_string
    ( 
        --(SELECT STRING_AGG(SELECTED, ',') FROM 
                (SELECT 
                    REPLACE(REPLACE(SUBSTRING(
                        PARAMETRI
                        ,CHARINDEX('=',PARAMETRI, PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI) ) +1
                        ,CHARINDEX(
                            CHAR(13)+CHAR(10)
                            ,PARAMETRI
                            ,PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI)
                        ) 
                        -CHARINDEX('=',PARAMETRI, PATINDEX('%ACCOUNTFILTERFST=%', PARAMETRI) )
                    ),CHAR(13), ''), CHAR(10), '') AS SELECTED
                FROM DBO.XXX
                    WHERE TIPO_DIMENSIONE = 'CONTO'
                    AND COD_CUBO = 'XPlus'
                --)  T
            
            )
    , ','
    )
)

dbo.XXX

+----------+-----------------+--------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COD_CUBO | TIPO_DIMENSIONE |         COD_DIMENSIONE         |                                                                                                                                                                                                                                                                                                                      PARAMETRI                                                                                                                                                                                                                                                                                                                      |
+----------+-----------------+--------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| XPlus    | AZI             | 8A8A81847FB7210E017FB73E244334 | #Fri Sep 23 09:10:04 UTC 2022 filtro=010 entityCTPCodeAttr=CTP All Entities Code hierarchyAttr=All Entities Hierarchy codeAttr=All Entities Code entityCTPDim=CTP All Entities entitySegmentHierarchyAttr=CTP for Segment All Entities Hierarchy entityCTPFlag=1 entitySegmentDescriptionAttr=CTP for Segment All Entities Description entitySegmentCodeAttr=CTP for Segment All Entities Code entityCTPDescriptionAttr=CTP All Entities Description entitySegmentFlag=1 nameDim=All Entities entityCTPHierarchyAttr=CTP All Entities Hierarchy entitySegmentDim=CTP for Segment All Entities descriptionAttr=All Entities Description entityFlag=0 |
+----------+-----------------+--------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

dbo.YYY

+------------+----------+------------------------------------+-----------------------------------+------------+------------+--------+-----------+-----------------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| COD_SCHEMA | COD_VOCE |             DESC_VOCE0             |            DESC_VOCE1             | DESC_VOCE2 | DESC_VOCE3 | FORMAT | TIPO_VOCE |     FORMULA_VOCE      | ORDINAMENTO | FLAG_NOREPORT | NAME_XBRL | TIPO_VOCE_XBRL | PROVENIENZA | USERUPD |     DATEUPD      | CLASSE | FLAG_CAMBIA_SEGNO | FLAG_FORZA_FORMULA_CUBO | FORMULA_CUBO | DESC_VOCE4 | DESC_VOCE5 | DESC_VOCE6 | DESC_VOCE7 | DESC_VOCE8 | DESC_VOCE9 | DESC_VOCE10 | DESC_VOCE11 | DESC_VOCE12 | DESC_VOCE13 | DESC_VOCE14 | DESC_VOCE15 | DESC_VOCE16 | DESC_VOCE17 | DESC_VOCE18 | DESC_VOCE19 | DESC_VOCE20 | DESC_VOCE21 | DESC_VOCE22 | DESC_VOCE23 | DESC_VOCE24 |
+------------+----------+------------------------------------+-----------------------------------+------------+------------+--------+-----------+-----------------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|         10 | 10       | Sales Revenue                      | Sales Revenue                     | NULL       | NULL       | 0      | A         | NULL                  | 10          |             0 | NULL      | I              | INPUT_WEB   | TNA     | 12.03.2017 18:54 | R      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         15 | 10       | Sales Revenue                      | Sales Revenue                     | NULL       | NULL       | 0      | A         | NULL                  | 10          |             0 | NULL      | I              | INPUT_WEB   | CCH     | 23.08.2018 14:41 | R      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         20 | 10       | ASSETS                             | ASSETS                            | NULL       | NULL       | 2      | D         | NULL                  | 10          |             0 | NULL      | I              | INPUT_WEB   | Sam     | 21.09.2016 21:23 | A      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         30 | RF001    | CASH FLOWS FROM OPERATING ACTIVITY | NULL                              | NULL       | NULL       | 9      | D         | NULL                  | RF001       |             0 | NULL      | I              | INPUT_WEB   | DOM     | 19.04.2017 01:28 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         40 | 10010    | Beginning Cash Balance             | NULL                              | NULL       | NULL       | 2      | A         | NULL                  | 10010       |             0 | NULL      | I              | INPUT_WEB   | TNA     | 06.05.2017 00:14 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         50 | 70       | Debt to Equity                     | NULL                              | NULL       | NULL       | NULL   | F         | {V,,'155'}/{V,,'150'} | 70          |             0 | NULL      | I              | INPUT_WEB   | CHARLIE | 19.04.2017 03:09 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         55 | 35       | Wages & Salary                     | Wages & Salary                    | NULL       | NULL       | 0      | A         | NULL                  | 0           |             0 | NULL      | I              | INPUT_WEB   | CCH     | 31.07.2020 15:20 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         60 | 55       | General & Administrative Expenses  | General & Administrative Expenses | NULL       | NULL       | 0      | A         | NULL                  | 0           |             0 | NULL      | I              | INPUT_WEB   | CCH     | 31.07.2020 15:22 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
|         65 | 10       | Net Sales                          | NULL                              | NULL       | NULL       | 0      | F         | {V,'010','020'}       | 0           |             0 | NULL      | I              | INPUT_WEB   | CCH     | 31.07.2020 15:33 | N      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
+------------+----------+------------------------------------+-----------------------------------+------------+------------+--------+-----------+-----------------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

Output Table:

+------------+----------+---------------+---------------+------------+------------+--------+-----------+--------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| COD_SCHEMA | COD_VOCE |  DESC_VOCE0   |  DESC_VOCE1   | DESC_VOCE2 | DESC_VOCE3 | FORMAT | TIPO_VOCE | FORMULA_VOCE | ORDINAMENTO | FLAG_NOREPORT | NAME_XBRL | TIPO_VOCE_XBRL | PROVENIENZA | USERUPD |     DATEUPD      | CLASSE | FLAG_CAMBIA_SEGNO | FLAG_FORZA_FORMULA_CUBO | FORMULA_CUBO | DESC_VOCE4 | DESC_VOCE5 | DESC_VOCE6 | DESC_VOCE7 | DESC_VOCE8 | DESC_VOCE9 | DESC_VOCE10 | DESC_VOCE11 | DESC_VOCE12 | DESC_VOCE13 | DESC_VOCE14 | DESC_VOCE15 | DESC_VOCE16 | DESC_VOCE17 | DESC_VOCE18 | DESC_VOCE19 | DESC_VOCE20 | DESC_VOCE21 | DESC_VOCE22 | DESC_VOCE23 | DESC_VOCE24 |
+------------+----------+---------------+---------------+------------+------------+--------+-----------+--------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|         10 |       10 | Sales Revenue | Sales Revenue | NULL       | NULL       |      0 | A         | NULL         |          10 |             0 | NULL      | I              | INPUT_WEB   | TNA     | 12.03.2017 18:54 | R      |                 0 |                       0 | NULL         | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        | NULL        |
+------------+----------+---------------+---------------+------------+------------+--------+-----------+--------------+-------------+---------------+-----------+----------------+-------------+---------+------------------+--------+-------------------+-------------------------+--------------+------------+------------+------------+------------+------------+------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+

The Function POSITION would be the one suitable if one goes by the logic of the function, but I am stuck with it because I am not able to pass it an starting position argument.

Tags: postgresql string

Answers

You can write your own function which like as CHARINDEX, I wrote sample function for you:

CREATE OR REPLACE FUNCTION position_start(find text, findin text, startin int4)
 RETURNS int4
 LANGUAGE plpgsql
AS $function$   
declare 
    vret int4;
begin 
    if startin = 0 then 
        startin = 1; 
    end if; 
    
    vret = position(find in substring(findin, startin, length(findin) - startin + 1));

    if vret = 0 then 
        return 0; 
    end if; 

    return startin - 1 + vret; 
exception 
    when others then 
        return 0;
end 
$function$
;

-- sample test
select position_start('o', 'Hello World', 1) => return: 5

select position_start('o', 'Hello World', 6) => return: 8

- NaomiA
- October 20, 2022 at 10:59 pm
- 0 votes
0
The POSITION() function

Example:
```
SELECT POSITION('our' in 'w3resource'); -- position =  6
```
w3schools has an article https://www.w3resource.com/PostgreSQL/position-function.php
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.