All Forums Database
eric_td 10 posts Joined 04/13
09 Jan 2015
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.

 

Rohan_Sawant 55 posts Joined 07/14
09 Jan 2015

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 4628 posts Joined 11/04
09 Jan 2015

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

 

Dieter

bhaskar3105 4 posts Joined 04/10
12 Jan 2015

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 10 posts Joined 04/13
13 Jan 2015

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

dnoeth 4628 posts Joined 11/04
13 Jan 2015

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.

Dieter

dnoeth 4628 posts Joined 11/04
13 Jan 2015

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

Dieter

eric_td 10 posts Joined 04/13
01 Jul 2015

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

You must sign in to leave a comment.