Separate column string by character on new columns

Transact-SQL
1

Hi All.
The table has column FirstName with value like:
Beatrix (Betty)
Hang "Winnie"
I would like to separate those values on two columns and result should be looks
col1 col2
Beatrix Betty
Hang Winnie
How to do that?

Thanks.

2

One way is to do this: is this what you want?

select
,''Beatrix,'' Betty
,''Hang,''Winnie
from your table

image

3

are these the only characters you want to replace namely
" ( and )?

Or are there other?

4

Hi yosiasz. Thanks for replay.
I would like remove quot { " } , left and right parentheses { ( ) } and separate contents of FirstName column.

Thanks

  • List item
5

replace(replace(replace(FirstName, '"', '') , '(', ''), ')','')

very ugly, fix it in the source app that populates it

6

And also I need to separate from FirstName column only part where we remove unwanted characters. For instance, if FirstName had value Smith Beatrix (Betty) and now we get Smith Beatrix Betty. As the result must be:
Smith Beatrix - one column and Betty other column.

Thanks

7 
drop table if exists #data
create table #data(name varchar(100))

insert into #data(name)
values('Beatrix (Betty)'),('Hang "Winnie"'),('Smith Beatrix (Betty)')

select  
	Left(name, Coalesce(quotePos -1, bracketPos -1, length)) As FirstName,
	Coalesce(
		Replace(SUBSTRING(name, quotePos + 1, length),'"',''),
		Replace(SUBSTRING(name, bracketPos + 1, length),')','')
	) As AlternateName
from #data
cross apply (select NullIf(PATINDEX('%"%',name),0) as quotePos, NullIf(PATINDEX('%(%',name),0) as bracketPos, len(name) as length) X
1 Like
8

Hi AndyC. Thanks for replay.

Your approach should be works for that hard coded value. But in my case I need to separate FirstName column which has thousand records. I'm thinking at the beginning need to separate value in FirstName column based on charters { " } , { ( } , { ) } and then remove those characters in created column. My problem how to separate FirstName column based in my case on characters { " } , { ( } or { ) } ?

Thanks.

9

I'm not sure I understand what you mean, that query will work regardless of how many rows you have in the table (#data in the example).

10

Hi AndyC.
I tried to modify your code for my task like:

insert into #data (name)
SELECT FName
FROM [dbo].[Employees]

select  
	Left(name, Coalesce(quotePos -1, bracketPos -1, length)) As FirstName,
	Coalesce(
		Replace(SUBSTRING(name, quotePos + 1, length),'"',''),
		Replace(SUBSTRING(name, bracketPos + 1, length),')','')
	) As AlternateName
from #data
cross apply (select NullIf(PATINDEX('%"%',name),0) as quotePos, NullIf(PATINDEX('%(%',FName),0) as bracketPos, len(name) as length) X

The result is correct. Now I need the result columns FirstName and AlternativeName update Employees table columns FName and Salutation accordingly?

Thanks.

11

Something like:

Update E 
Set
	FName = Left(FName, Coalesce(quotePos -1, bracketPos -1, length)),
	Salutation = Coalesce(
		Replace(SUBSTRING(FName, quotePos + 1, length),'"',''),
		Replace(SUBSTRING(FName, bracketPos + 1, length),')','')
	)
from [dbo].[Employees] E
cross apply (select NullIf(PATINDEX('%"%',FName),0) as quotePos, NullIf(PATINDEX('%(%',FName),0) as bracketPos, len(FName) as length) X
12

Thanks AndyC. It works awesome.