By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table #Names(
[FirstName] [varchar](120)
)
insert into #Names (FirstName)
values
('"Le Firstname" MiddleName SomeName'),
(char(13)+char(10)+'Copy Paste'+char(9)+'King'),
('Paul'),
('King "foo bar" "the 3rd"')
4 rows affected
select * from #Names;
| FirstName |
|---|
| "Le Firstname" MiddleName SomeName |
Copy Paste King |
| Paul |
| King "foo bar" "the 3rd" |
SELECT
FirstName,
SUBSTRING(FirstNameORG,LEN(FirstName)+1,LEN(FirstNameORG)) as MiddleNames
FROM (
SELECT
CASE WHEN firstSpace < FirstDoubleQuoot
THEN SUBSTRING(FirstName,1,firstSpace-1)
WHEN secondDoublequoot > firstDoublequoot
THEN SUBSTRING(FirstName,firstDoublequoot-1,secondDoublequoot-firstDoublequoot+2)
WHEN firstSpace>1
THEN SUBSTRING(FirstName,1,firstSpace-1)
ELSE FirstName
END as FirstName,
FirstName as FirstNameORG
FROM (
SELECT
FirstName,
CHARINDEX(' ',FirstName) firstSpace,
CHARINDEX('"',FirstName) firstDoubleQuoot,
CHARINDEX('"',FirstName,CHARINDEX('"',FirstName)+2) secondDoubleQuoot
FROM #Names
) x
) y;
| FirstName | MiddleNames |
|---|---|
| "Le Firstname" | MiddleName SomeName |
Copy |
Paste King |
| Paul | |
| King | "foo bar" "the 3rd" |