SQL query logic

Transact-SQL
1

Hi,

I have source table with the below column values.

AA12.F07.123456
XX34 SA8 676868
YY13_SS3_798798
HJ88.657769
.898798

I want to split that into below,

AA12 F07 123456
XX34 SA8 676868
HJ88 657769
898798

Can you please help me how to split the data as above?

Thanks,
Dany

2

--Drop table pharses
create table pharses
(
Terms char(50)
)

insert into pharses values ('AA12.F07.123456')
insert into pharses values ('XX34 SA8 676868')
insert into pharses values ('YY13_SS3_798798')
insert into pharses values ('HJ88.657769')
insert into pharses values ('.898798')
insert into pharses values('AA3.B5.8999')

select replace(Terms,substring(Terms,PATINDEX('%[,~,@,#,$,%,&,*,(,),.,!,_]%',Terms),1),' ')
from pharses

3

I think this solution is simpler
Please correct me if i am wrong

Create Data Script 
use tempdb 
go 

Drop table #pharses
create table #pharses
(
Terms char(50)
)

insert into #pharses values ('AA12.F07.123456')
insert into #pharses values ('XX34 SA8 676868')
insert into #pharses values ('YY13_SS3_798798')
insert into #pharses values ('HJ88.657769')
insert into #pharses values ('.898798')
insert into #pharses values('AA3.B5.8999')
go
SQL 
SELECT  *Replace* (terms, '.', ' ') 
FROM #pharses 

go
Result

image

4

If a string contain any special character that can be .,$,*,_ etc then i think the script written by you won't work.If you see the 3rd row which is YY13_SS3_798798 is not replaced with blank spaces as per your script.

5

After I posted

I think this solution is simpler
Please correct me if i am wrong

I took a look at it and understood
but did not reply ,,

thank you for explaining