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

Database
Enthusiast

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

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.

7 REPLIES
Enthusiast

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

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

Junior Contributor

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

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]*$')
Enthusiast

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

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

Enthusiast

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

Dieter, Rohan,

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

Thanks

Junior Contributor

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

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.

Junior Contributor

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

Hi Eric,

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

Enthusiast

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

Thanks All,

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