can you help me to split a string (or preferred if possible, place a blank at every "split" occasion) after every change from alphanumeric to numeric and vice versa ?
So a string like D2c1 22 should look like D 2 c 1 22. Best way from would be to put a blank at every change from alpha-numeric to numeric.
You can use this regexp to find the places where it switches:
(?<=\d)(?=\D)|(?<=\D)(?=\d)
This way:
"234kjh23ljkgh34klj2345klj".gsub(/(?<=\d)(?=\D)|(?<=\D)(?=\d)/, " ")
=> "234 kjh 23 ljkgh 34 klj 2345 klj"
Edit: Without zero length look ahead and look behind:
"234kjh23ljkgh34klj2345klj".gsub(/(\d)(\D)/, "#{$1} #{$2}").gsub(/(\D)(\d)/, "#{$2} #{$1}")
=> "23 jk 5 jkgk 5 lk 534 lj"
regress=> select regexp_replace( '234kjh23ljkgh34klj2345klj', '(?<=\d)(?=\D)|(?<=\D)(?=\d)', ' ', 'g'); ERROR: invalid regular expression: quantifier operand invalid . See sqlfiddle.com/#!12/d41d8/148
Here's an approach tested with PostgreSQL and verified to work. It's a bit tortured, so performance might be ... interesting.
CREATE AGGREGATE array_cat_agg (
BASETYPE = anyarray,
SFUNC = array_cat,
STYPE = anyarray
);
SELECT array_to_string(array_cat_agg(a), ' ')
FROM regexp_matches('234kjh23ljkgh34klj2345klj', '(\D*)(\d*)', 'g') x(a);
We need array_cat_agg because regular array_agg can't aggregate arrays of arrays.
Alternately, a form of @davidrac's approach that'll work with PostgreSQL and probably perform significantly better (though I haven't tested) is:
SELECT regexp_replace(
regexp_replace(
'234kjh23ljkgh34klj2345klj', '(\d)(\D)', '\1 \2', 'g'
), '(\D)(\d)', '\1 \2', 'g');
This is executing the replacement in two passes. First it's inserting a space where series of digits end and series of non-digits begin. Then in another pass it's inserting spaces where series of non-digits end and series of digits begin.
Update: Here's an improved formulation:
SELECT trim(regexp_replace('234kjh23ljkgh34klj2345klj', '(?!\d)(\D+)|(?!\D)(\d+)', '\1\2 ', 'g'));
standard_conforming_strings change? Try SET standard_conforming_strings = on; then test again. (This is why you always mention your PostgreSQL version in questions). If it works with standard_conforming_strings on, then (a) upgrade PostgreSQL and (b) see postgresql.org/docs/current/static/… for how to make the query work without upgrading PostgreSQL. Basically, instead of '\' use E'\\'; double backslashes and use E''.'(\d)(\D)' becomes E'(\\d)(\\D) and so on. You need to urgently start planning to upgrade your end-of-life and obsolete PostgreSQL by the way, and always mention your version in questions especially since it's so incredibly obsolete. See also postgresql.org/support/versioning .Best way from would be to put a blank at every change from alpha-numeric to numeric.
Its not hard to do:
$ echo "D2c1 22" | sed 's|\([a-ZA-Z]\)\([0-9]\)|\1 \2|g;s|\([0-9]\)\([a-ZA-Z]\)|\1 \2|g'
D 2 c 1 22
Here I used sed and its regexp because you doesn't mention which language you use. Main idea is to use 2 regexp which replaces alpha with digit and digit with alpha to first character, space and second character.
You can match using the regex
(?<=[a-z])(?=[0-9])|(?<=[0-9])(?=[a-z])
and replace it with a space.
\d and \D too, so you can cope with any digit and non-digit sequences, not just lower-case alphanumeric.