i want to use the below values into different columns inside the table now they are storing into one i tried REGEXP_SUBSTR but still i can't use them separately .. any help would be apricated

`

SELECT
REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ', 1, level) AS parts
FROM dual
CONNECT BY REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ', 1, level) IS NOT NULL;


the output should be like this : 

column A : One Thousand
column B : Four Hundred
column C : Forty Six

`

the above is my attempt

CodePudding user response：

If you change your pattern to:

'[^ ] ( [^ ] )?'

then it will find one word optionally followed by a space and another word - so it would still work if you started with an odd number of words.

Then your query could be:

SELECT
REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ( [^ ] )?', 1, level) AS parts
FROM dual
CONNECT BY REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ( [^ ] )?', 1, level) IS NOT NULL;

fiddle

If you want them split into separate columns rather than rows then you would need to know how many to allow for - i.e. the maximum number of words you might have to handle. Then instead of a hierarchical query you can do:

SELECT
REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ( [^ ] )?', 1, 1) AS part1,
REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ( [^ ] )?', 1, 2) AS part2,
REGEXP_SUBSTR('One Thousand Four Hundred Forty Six', '[^ ] ( [^ ] )?', 1, 3) AS part3
FROM dual

fiddle

CodePudding user response：

You have the regex [^ ] that find a word (adjacent characters with no blank).

You want a regex that finds two words (a word, a blank, a word): [^ ] [^ ] .

SELECT
REGEXP_SUBSTR(str, '[^ ]  [^ ] ', 1, level) AS parts
FROM (select 'One Thousand Four Hundred Forty Six' as str from dual)
CONNECT BY REGEXP_SUBSTR(str, '[^ ]  [^ ] ', 1, level) IS NOT NULL;