Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Need to Find the Occurrence of an Alphabet in a String

Hello Team,
I need to find the occurrence of an alphabet in a String.
Eg. In the String "5744604SOS032011000", I need to find the position of the 1st occurrence of the alphabet.
In this example it is "S", but it can have any alphabet value from A to Z.
Is there an Vertica Equivalent of PATINDEX that we use in SQL. Thank You.
Warm Regards,
Jayanta

Best Answers

Answers

  • Thank You @mosheg and @Jim_Knicely .
    I'm using Celonis, a process mining tool which supports Vertica SQL. Below is the Code that I wrote for transformation, but Im getting an error.
    INSERT INTO "_CEL_P2P_ACTIVITIES"("_CASE_KEY","ORDER") SELECT "TEST_CSV"."CEWACN" AS "_CASE_KEY", STRPOS(RTRIM(LTRIM("TEST_CSV"."CEWACN"),REGEXP_SUBSTR(RTRIM(LTRIM("TEST_CSV"."CEWACN"),'[A-Z]')))) AS "ORDER" FROM "TEST_CSV";

    Following is the Error Message I got,
    Execution error: [Vertica]VJDBC ERROR: Function REGEXP_SUBSTR(varchar) does not exist, or permission is denied for REGEXP_SUBSTR(varchar)
    Any Idea about the error.

  • Thank You @Jim_Knicely .. It worked..Indeed it was a parenthesis issue..:-)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.