I have a table with just one column and about 3000 rows that follows the same pattern. I want to include the days of the week at the beginning of the string for each TIME inside the same string in the row.
Original table :
| DATA_TIME_COMBINATION |
|---|
| Monday 3 am HM, 5 am HC, 10 pm HX |
| Saturday AB 6 am, WE 5 pm |
| Sunday Friday AN 50 TU,FG 55 RE, DC 56 JJ |
| Tuesday 10 am, 5 am, 1 am |
| Wednesday 12 pm-9 pm- 1am |
Results needed :
| DATA_TIME_COMBINATION |
|---|
| Monday 3 am HM, Monday 5 am HC, Monday 10 pm HX |
| Saturday AB 6 am, Saturday WE 5 pm |
| Sunday Friday AN 50 TU, Sunday Friday FG 55 RE, Sunday Friday DC 56 JJ |
| Tuesday 10 am, Tuesday 5 am, Tuesday 1 am |
| Wednesday 12 pm- Wednesday 9 pm- Wednesday 1am |
Thanks in advance!
CodePudding user response：
seems you want replace the comma with a comma and the first word at beginning of the string
select replace(DATA_TIME_COMBINATION, ',',
concat(',', substr(DATA_TIME_COMBINATION,1,charindex(' ', DATA_TIME_COMBINATION)-1)))
from my_table
CodePudding user response：
Once you know the character and days.
Then it'll become a simple replace.
One cross apply for the weekdays.
A second for a character in place.
SELECT
REPLACE(REPLACE([DATA_TIME_COMBINATION], chr, chr ' ' days ' '),' ',' ') as DATA_TIME_COMBINATION
FROM mytable AS t
CROSS APPLY (
SELECT STRING_AGG(value, ' ') days
FROM STRING_SPLIT(
LEFT([DATA_TIME_COMBINATION], PATINDEX('%[a-z] [0-9]%', [DATA_TIME_COMBINATION])), ' ') spl
WHERE value LIKE '