Best Answers

• mosheg Vertica Employee Administrator

  August 2020 edited August 2020 Answer ✓

  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)
  

  6
• mosheg Vertica Employee Administrator

  August 2020 Answer ✓

  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)
  
  

  6
• Jim_Knicely - Select Field - Administrator

  August 2020 Answer ✓

  @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)
  

  5
• Jim_Knicely - Select Field - Administrator

  August 2020 edited August 2020 Answer ✓

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

  5