I need help understanding how to add a hyphen to a column where the values are as follows, 8601881, 9700800,2170
The hyphen is supposed to be just before the last digit. There are multiple such values in the column and the length of numbers could be 5,6 or more but the hyphen has to be before the last digit.
Any help is greatly appreciated.
The expected output should be as follows, 860188-1,970080-0,217-0
Advertisement
Answer
select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;create table data(value varchar(100));
Here is the full example:
create table data(value varchar(100));
insert into data values('6789567');
insert into data values('98765434');
insert into data values('1234567');
insert into data values('876545');
insert into data values('342365');
select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;
| (No column name) |
| :--------------- |
| 678956-7 |
| 9876543-4 |
| 123456-7 |
| 87654-5 |
| 34236-5 |
In case OP meant there can be multiple numbers in the column value here is the solution:
create table data1(value varchar(100));
insert into data1 values('6789567,5467474,846364');
insert into data1 values('98765434,6474644,76866,68696');
insert into data1 values('1234567,35637373');
select t.value, string_agg(concat(substring(token.value, 1, len(token.value)-1), '-',
substring(token.value, len(token.value), 1)), ',') as result
from data1 t cross apply string_split(value, ',') as token group by t.value;
value | result
:--------------------------- | :-------------------------------
1234567,35637373 | 123456-7,3563737-3
6789567,5467474,846364 | 678956-7,546747-4,84636-4
98765434,6474644,76866,68696 | 9876543-4,647464-4,7686-6,6869-6