Simple Question
I have the following type of results in a string field
'Number=123456'
'Number=1234567'
'Number=12345678'
How do I extract the value from the string with regard that the value can change between 5-8 figures
So far I did this but I doubt that fits my requirement
SELECT substring('Size' from 8 for ....
If I can tell it to start from the = sign till the end that would help!
The likely simplest solution is to trim 7 leading characters with right():
right(str, -7)
Demo:
SELECT str, right(str, -7)
FROM (
VALUES ('Number=123456')
, ('Number=1234567')
, ('Number=12345678')
) t(str);
str | right
-----------------+----------
Number=123456 | 123456
Number=1234567 | 1234567
Number=12345678 | 12345678
Based on this question: Split comma separated column data into additional columns
You could probably do the following:
SELECT *, split_part(col, '=', 2)
FROM table;
You may use regexp_matches :
with t(str) as
(
select 'Number=123456' union all
select 'Number=1234567' union all
select 'Number=12345678' union all
select 'Number=12345678x9'
)
select t.str as "String",
regexp_matches(t.str, '=([A-Za-z0-9]+)', 'g') as "Number"
from t;
String Number
-------------- ---------
Number=123456 123456
Number=1234567 1234567
Number=12345678 12345678
Number=12345678x9 12345678x9
--> the last line shows only we look chars after equal sign even if non-digit
