How to replace spaces in text at a specified position in T-SQL-CodePudding

I have a column called Test for saving the char eg.

A:Hi B: 13/06/2022 C:Peter

and then use string_split() to extract the B

declare @test varchar(100) = replace('A:Hi C:Peter B: 13/06/2022',' ','')

select * from string_split(@test,' ')
where [value] like 'B%'

After code result

A:HiB:13/06/2022C:Peter

Expect result:

A:Hi B:13/06/2022 C:Peter

How to remove the space between B and date of Test column ?
I tried to used REPLACE,ltrim,rtrim, but it is not working any suggest or idea? Thank you.

CodePudding user response：

declare @test varchar(100) = replace('A:Hi C:Peter B: 13/06/2022','B: ','B:')

select * from string_split(@test,' ')
where [value] like 'B%'