CODEWITHSUNDEEP
mysqlcratecratedb
goosebump
goosebump

Reputation: 96

Split string in MYSQL via CRATE

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?

Upvotes: -2

Views: 168

Answers (3)

metase
metase

Reputation: 1309

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

Upvotes: 0

Atul Akabari
Atul Akabari

Reputation: 103

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

SUBSTRING_INDEX comes in handy here:

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

enter image description here

Upvotes: 0

Related Questions