Need to Find the Occurrence of an Alphabet in a String

JayantaB

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

mosheg

Try this:

WITH
my_list (my_string) AS (
          SELECT '1234567890abc1234567890'
UNION ALL SELECT '1234I LOVE VERTICA192021'
UNION ALL SELECT 'If knowledge is not put into practice, it does not benefit one.'
UNION ALL SELECT '1234567890aa + bb = aabb'
UNION ALL SELECT '123 AbcdefghijklmnopqrstuvwxyZ'
)
SELECT my_string,STRPOS(my_string,REGEXP_SUBSTR(my_string,'[A-Z,a-z]')) as 'First occurrence of an alphabet'
FROM my_list;
                            my_string                                                                           | First occurrence of an alphabet
----------------------------------------------------------------------+---------------------------------
 1234567890abc1234567890                                                             |                              11
 1234I LOVE VERTICA192021                                                               |                                5
 If knowledge is not put into practice, it does not benefit one. |                                1
 1234567890aa + bb = aabb                                                                  |                              11
 123 AbcdefghijklmnopqrstuvwxyZ                                                     |                                5
(5 rows)

mosheg

Or this:

CREATE FUNCTION PUBLIC.FIND_FIRST_ALPHA_POS(X VARCHAR(100)) RETURN INT
AS
BEGIN RETURN
 STRPOS(X,REGEXP_SUBSTR(X,'[A-Z,a-z]'));
END;

SELECT FIND_FIRST_ALPHA_POS('5744604SOS032011000');

FIND_FIRST_ALPHA_POS
----------------------
                    8
(1 row)

Jim_Knicely

@JayantaB - As with most things, there are a million different ways to do everything

Here's yet another option:

 dbadmin=> SELECT REGEXP_INSTR('5744604SOS032011000', '[A-Z,a-z]');
 REGEXP_INSTR
--------------
            8
(1 row)

dbadmin=> CREATE OR REPLACE FUNCTION patindex (x VARCHAR) RETURN INT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN REGEXP_INSTR(x, '[A-Z,a-z]');
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT patindex('5744604SOS032011000');
 patindex
----------
        8
(1 row)

Using @mosheg's great example:

dbadmin=> WITH
dbadmin-> my_list (my_string) AS (
dbadmin(>           SELECT '1234567890abc1234567890'
dbadmin(> UNION ALL SELECT '1234I LOVE VERTICA192021'
dbadmin(> UNION ALL SELECT 'If knowledge is not put into practice, it does not benefit one.'
dbadmin(> UNION ALL SELECT '1234567890aa + bb = aabb'
dbadmin(> UNION ALL SELECT '123 AbcdefghijklmnopqrstuvwxyZ'
dbadmin(> )
dbadmin-> SELECT my_string, patindex(my_string) as 'First occurrence of an alphabet'
dbadmin-> FROM my_list;
                            my_string                            | First occurrence of an alphabet
-----------------------------------------------------------------+---------------------------------
 1234567890abc1234567890                                         |                              11
 1234I LOVE VERTICA192021                                        |                               5
 If knowledge is not put into practice, it does not benefit one. |                               1
 1234567890aa + bb = aabb                                        |                              11
 123 AbcdefghijklmnopqrstuvwxyZ                                  |                               5
(5 rows)

Jim_Knicely

I think your paranthesis are not aligned correctly. Make sure you are passing in 2 arguments to REGEXP_SUBSTR:

Like this:

REGEXP_SUBSTR(RTRIM(LTRIM("TEST_CSV"."CEWACN")),'[A-Z]')

Examples:

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";

Or if using REGEXP_INSTR:

INSERT INTO "_CEL_P2P_ACTIVITIES"("_CASE_KEY","ORDER")
SELECT "TEST_CSV"."CEWACN" AS "_CASE_KEY", REGEXP_INSTR(RTRIM(LTRIM("TEST_CSV"."CEWACN")), '[A-Z]') AS "ORDER"
  FROM "TEST_CSV";

JayantaB

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.

JayantaB

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