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?

It is entirely unclear what you are asking here. Please edit your question, and show some sample data, along with whatever SQL you have already tried. — Tim Biegeleisen, Nov 15 '18 at 05:45
Duplicate of https://stackoverflow.com/questions/34992575/mysql-substring-extraction-using-delimiter (which is again a duplicate)? — Caesar, Nov 15 '18 at 05:55
Why don't you use procedure for this issue. I dont think with simple create query you can able to do it. — Kandy, Nov 15 '18 at 06:03
Please provide your requirement that what you exactly want to do.. — Kandy, Nov 15 '18 at 06:11

score 0 · Answer 1 · answered Nov 15 '18 at 05:57

0

SUBSTRING_INDEX comes in handy here:

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

answered Nov 15 '18 at 05:57

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · edited Nov 15 '18 at 10:39

0

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

edited Nov 15 '18 at 10:39

Madhur Sharma

132
7

answered Nov 15 '18 at 06:11

Atul Akabari

93
4

score 0 · Answer 3 · answered Dec 29 '18 at 00:55

0

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

answered Dec 29 '18 at 00:55

metase

1,169
2
16
29

Split string in MYSQL via CRATE

3 Answers3