Split string in MYSQL via CRATE

Question

I want to split a string in CRATE. I tried with substr, but it takes only substr(string,long,long). I want something like a function which can take delimiter string.

Example :

value=1234-5656

select SUBSTR(value, '-',1) as first from XYZ;

I want to split the value into 1234 and 5656 in a SQL query. But CRATE does not support SUBSTR(value, '-',1). So I am looking for an option to split the value in the CRATE query.

Any help?

Answer 1

SUBSTRING_INDEX comes in handy here:

SELECT
    SUBSTRING_INDEX('1234-5656', '-', 1)  AS first,
    SUBSTRING_INDEX('1234-5656', '-', -1) AS second
FROM yourTable;

Answer 2

please try using this query:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, '_', 1), '_', -1) as beforesplit, SUBSTRING_INDEX(SUBSTRING_INDEX(name, '_', 2), '_', -1) as aftersplit FROM testenter

Answer 3

For CrateDB you probably want to use regex_matches function more info on Create's documentation site

However the following should give you what you're looking for

select regexp_matches(yourColumnName, '([0-9])\w+')[1] from yourTable