Hi,
I want to separate fields like example
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
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]*$')
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
Dieter, Rohan,
Thanks for you replies. But sadly I need to use it on TD12.
Thanks
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.
Hi Eric,
are there any UDFs at your site or is there any chance you can install one?
Thanks All,
DB was migrated to 14.10, so i did implement as required.