SQLTeam.com | Weblogs | Forums

Need help to separate column into two


#1

i took screenshot of my screen.
there's name and i need to separate full name into 2 category...firstname and last name.
i thought it would work with case and decode..
but once I've done it with case or decode , and try first, or last name , thing i did is gone.
like foto I uploaded....

any other idea ?

Thank you


#2

Should be possible to "split" based on the first Upper Case letter (not in 1st character position).

However, you are using Oracle and as this is a Microsoft SQL Server forum you may find that folk here (me included!) don't know the Oracle-specific function names that would be required.

This works in SQL Server, except for the "De HaanLex" example - space followed by capital letter would need special handling. You could find the "first capital letter starting from the right" instead, but what about "LastnameAnneMarie" and similar? Perhaps "First capital letter not proceeded by a space" might work?

SELECT	LEFT([name], Offset) AS FirstName
	, RIGHT([name], LEN([Name])
                       - Offset
                       - CASE WHEN SUBSTRING([name], Offset+1, 1) = '%'
                              THEN 1 ELSE 0 END
          )
FROM
(
	SELECT	PATINDEX('%[%A-Z]%', STUFF([name], 1, 1, '')
                                       COLLATE Latin1_General_BIN2) AS Offset
		, [name]
	FROM
	(
		SELECT 'King%Steven' AS [name] UNION ALL
		SELECT 'De HaanLex' UNION ALL
		SELECT 'GreenbergNancy' UNION ALL
		SELECT 'Firstonly'
	) AS X
) AS Y