Find position of first non-digit or space from the back

[eric_td]

Hi,

I want to separate fields like example

Field_source         O/p field1      O/p field2

abc def 234          abc def           234

xyzyzy                   xyzyzy 

24 hours 5656        24 hours          5656

High7stars 675       High7stars        675   

I have used the function position('003' in char2hexint(translate(col_name using latin_to_unicode))) to find the postion of number in string.

But this works fine for cases of first two examples, but fails when a number is present in middle etc.

Basically I need to find the position of first space or first non-digit from the back. If I get that then I can use substr and divide.

Please let me know of any possible workaround.

Thanks.

[Rohan_Sawant]

Hi Eric_td,

The below query would do the required

CREATE MULTISET VOLATILE TABLE VT_TEST
(
  TXT VARCHAR(30)
)
ON COMMIT PRESERVE ROWS;
 
INSERT INTO VT_TEST ('abc def 234');
INSERT INTO VT_TEST ('xyzyzy');
INSERT INTO VT_TEST ('24 hours 5656');
INSERT INTO VT_TEST ('High7stars 675');

WITH RECURSIVE REMOVE_SPACE (TXT,SPACE_REMOVED,LEVELS) AS
(
  SELECT 
    TXT
  , SUBSTRING(TXT FROM INDEX(TXT,' ') + 1) AS SPACE_REMOVED
  , 1 AS LEVELS
  FROM 
   VT_TEST
  UNION ALL
  SELECT 
    A.TXT
  , SUBSTRING(A.SPACE_REMOVED FROM INDEX(A.SPACE_REMOVED,' ') + 1) AS SPACE_REMOVED
  , LEVELS + 1
  FROM 
   REMOVE_SPACE A
  JOIN
   VT_TEST B
  ON A.TXT = B.TXT
  AND INDEX(SPACE_REMOVED,' ') <> 0
)
SELECT
DISTINCT 
  A.TXT
, CASE
 WHEN TRIM(OREPLACE(A.TXT,A.SPACE_REMOVED)) = ''
 THEN A.TXT
 ELSE OREPLACE(A.TXT,A.SPACE_REMOVED)
  END AS FIELD1
, OREPLACE(A.TXT,FIELD1) AS FIELD2
FROM 
 REMOVE_SPACE A
INNER JOIN
(
  SELECT
    TXT
  , MAX(LEVELS) AS MAX_LEVELS
  FROM
   REMOVE_SPACE
  GROUP BY 1
) B
ON A.TXT = B.TXT
AND A.LEVELS = B.MAX_LEVELS;

Please let me know in case of issues.

Thanks,

Rohan Sawant

[dnoeth]

What's your TD release?

Regular expressions can easily extract both fields looking for a group of digits at the end of the string:

field1:
REGEXP_SUBSTR(txt, '.+?(?=[0-9]*$)')

field2:
REGEXP_SUBSTR(txt, '[0-9]*$') 

[bhaskar3105]

Hi Dieter,

Could you give me more samples on the above. Is this fuction a combination of SUBSTRING + POSITION function in the earlier TD version?

Thanks,

Bhaskar

[eric_td]

Dieter, Rohan,

Thanks for you replies. But sadly I need to use it on TD12.

Thanks

[dnoeth]

Hi Bhaskar,

it's a substring based on a Regular Expression, TD14 also supports REGEXP_SIMILAR (extended LIKE), REGEXP_INSTR (extended POSITION), REGEXP_REPLACE and REGEXP_SPLIT_TO_TABLE (splitting a string into multiple rows).

Regular Expressions are vastly expanded wildcard searches (e.g. Unix GREP), there are lots of online resources how to write them, you'll find pre-defined expression for almost everything.

[dnoeth]

Hi Eric,

are there any UDFs at your site or is there any chance you can install one?

[eric_td]

Thanks All,

DB was migrated to 14.10, so i did implement as required.