Comment On SQL Splits

"I should probably start by saying that I am not a SQL expert," Paul wrote. "Sure, I've written my share of SELECT statements, plenty of UPDATE statements, and even a few ALTER statements, but beyond that... nada." [expand full text]

"I should probably start by saying that I am not a SQL expert," Paul wrote. "Sure, I've written my share of SELECT statements, plenty of UPDATE statements, and even a few ALTER statements, but beyond that... nada."

"Fortunately, we do have a SQL guru at work that I can learn from, and thankfully, his code is right there, in the database. Imagine my delight when I uncovered this 2,045-line snippet of SQL Magic that, so far as my non-expert mind can tell, attempts to split a name like 'HARTLEY JR' into two parts."

--SPLIT consultant SURNAME/FORENAMES

SELECT CLINICIAN.clinician_code AS CODE, 
	CLINICIAN.clinician_name AS [NAME], 
	LEN(CLINICIAN.clinician_name) AS LENGTH, 
	RIGHT(CLINICIAN.clinician_name, 4) AS INITS, 
	SPECIALTY.specialty_code AS SPEC_CODE, 
	SPECIALTY.specialty_name AS SPEC_NAME
INTO #clin_length
FROM CLINICIAN LEFT OUTER JOIN
	SPECIALTY 
	ON CAST(CLINICIAN.clin_specialty AS VARCHAR) = CAST(SPECIALTY.SPECIALTY AS VARCHAR)
ORDER BY LEN(CLINICIAN.clinician_name) DESC

SELECT CODE, 
	[NAME], RIGHT(INITS, 1) AS INITIALS, 
	LENGTH AS LENGTH, 
	'1' AS INITS_LENGTH,
	SPEC_CODE, 
	SPEC_NAME
INTO #inits1
FROM #clin_length
WHERE SUBSTRING(INITS, 3, 1) = ' '

SELECT CODE, 
	[NAME], RIGHT(INITS, 2) AS INITIALS, 
	LENGTH AS LENGTH, 
	'2' AS INITS_LENGTH,
	SPEC_CODE, 
	SPEC_NAME
INTO #inits2
FROM #clin_length
WHERE SUBSTRING(INITS, 2, 1) = ' '

SELECT CODE, 
	[NAME], RIGHT(INITS, 3) AS INITIALS, 
	LENGTH AS LENGTH, 
	'3' AS INITS_LENGTH,
	SPEC_CODE, 
	SPEC_NAME
INTO #inits3
FROM #clin_length
WHERE SUBSTRING(INITS, 1, 1) = ' '

SELECT *
INTO #all_inits
FROM #inits1
UNION
SELECT *
FROM #inits2
UNION
SELECT *
FROM #inits3

SELECT CODE, 
	LEFT([NAME], 1) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur3_1
FROM #all_inits
WHERE LENGTH = '3'
	AND INITS_LENGTH = '1'

SELECT CODE, 
	LEFT([NAME], 2) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur4_1
FROM #all_inits
WHERE LENGTH = '4'
	AND INITS_LENGTH = '1'

SELECT CODE, 
	LEFT([NAME], 1) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur4_2
FROM #all_inits
WHERE LENGTH = '4'
	AND INITS_LENGTH = '2'

SELECT CODE, 
	LEFT([NAME], 3) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur5_1
FROM #all_inits
WHERE LENGTH = '5'
	AND INITS_LENGTH = '1'

SELECT CODE, 
	LEFT([NAME], 2) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur5_2
FROM #all_inits
WHERE LENGTH = '5'
	AND INITS_LENGTH = '2'

SELECT CODE, 
	LEFT([NAME], 1) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur5_3
FROM #all_inits
WHERE LENGTH = '5'
	AND INITS_LENGTH = '3'

SELECT CODE, 
	LEFT([NAME], 4) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur6_1
FROM #all_inits
WHERE LENGTH = '6'
	AND INITS_LENGTH = '1'

SELECT CODE, 
	LEFT([NAME], 3) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur6_2
FROM #all_inits
WHERE LENGTH = '6'
	AND INITS_LENGTH = '2'

SELECT CODE, 
	LEFT([NAME], 2) AS SURNAME, 
	INITIALS, 
	SPEC_CODE, 
	SPEC_NAME
INTO #sur6_3
FROM #all_inits
WHERE LENGTH = '6'
	AND INITS_LENGTH = '3'


SELECT CODE,
	LEFT([NAME], 5) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur7_1
FROM #all_inits
WHERE LENGTH = '7'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 4) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur7_2
FROM #all_inits
WHERE LENGTH = '7'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 3) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur7_3
FROM #all_inits
WHERE LENGTH = '7'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 6) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur8_1
FROM #all_inits
WHERE LENGTH = '8'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 5) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur8_2
FROM #all_inits
WHERE LENGTH = '8'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 4) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur8_3
FROM #all_inits
WHERE LENGTH = '8'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 7) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur9_1
FROM #all_inits
WHERE LENGTH = '9'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 6) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur9_2
FROM #all_inits
WHERE LENGTH = '9'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 5) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur9_3
FROM #all_inits
WHERE LENGTH = '9'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 8) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur10_1
FROM #all_inits
WHERE LENGTH = '10'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 7) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur10_2
FROM #all_inits
WHERE LENGTH = '10'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 6) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur10_3
FROM #all_inits
WHERE LENGTH = '10'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 9) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur11_1
FROM #all_inits
WHERE LENGTH = '11'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 8) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur11_2
FROM #all_inits
WHERE LENGTH = '11'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 7) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur11_3
FROM #all_inits
WHERE LENGTH = '11'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 10) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur12_1
FROM #all_inits
WHERE LENGTH = '12'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 9) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur12_2
FROM #all_inits
WHERE LENGTH = '12'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 8) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur12_3
FROM #all_inits
WHERE LENGTH = '12'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 11) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur13_1
FROM #all_inits
WHERE LENGTH = '13'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 10) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur13_2
FROM #all_inits
WHERE LENGTH = '13'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 9) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur13_3
FROM #all_inits
WHERE LENGTH = '13'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 12) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur14_1
FROM #all_inits
WHERE LENGTH = '14'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 11) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur14_2
FROM #all_inits
WHERE LENGTH = '14'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 10) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur14_3
FROM #all_inits
WHERE LENGTH = '14'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 13) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur15_1
FROM #all_inits
WHERE LENGTH = '15'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 12) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur15_2
FROM #all_inits
WHERE LENGTH = '15'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 11) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur15_3
FROM #all_inits
WHERE LENGTH = '15'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 14) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur16_1
FROM #all_inits
WHERE LENGTH = '16'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 13) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur16_2
FROM #all_inits
WHERE LENGTH = '16'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 12) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur16_3
FROM #all_inits
WHERE LENGTH = '16'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 15) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur17_1
FROM #all_inits
WHERE LENGTH = '17'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 14) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur17_2
FROM #all_inits
WHERE LENGTH = '17'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 13) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur17_3
FROM #all_inits
WHERE LENGTH = '17'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 16) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur18_1
FROM #all_inits
WHERE LENGTH = '18'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 15) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur18_2
FROM #all_inits
WHERE LENGTH = '18'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 14) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur18_3
FROM #all_inits
WHERE LENGTH = '18'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 17) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur19_1
FROM #all_inits
WHERE LENGTH = '19'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 16) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur19_2
FROM #all_inits
WHERE LENGTH = '19'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 15) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur19_3
FROM #all_inits
WHERE LENGTH = '19'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 19) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur20_1
FROM #all_inits
WHERE LENGTH = '20'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 18) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur20_2
FROM #all_inits
WHERE LENGTH = '20'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 17) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur20_3
FROM #all_inits
WHERE LENGTH = '20'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 19) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur21_1
FROM #all_inits
WHERE LENGTH = '21'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 18) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur21_2
FROM #all_inits
WHERE LENGTH = '21'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 17) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur21_3
FROM #all_inits
WHERE LENGTH = '21'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 20) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur22_1
FROM #all_inits
WHERE LENGTH = '22'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 19) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur22_2
FROM #all_inits
WHERE LENGTH = '22'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 18) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur22_3
FROM #all_inits
WHERE LENGTH = '22'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 21) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur23_1
FROM #all_inits
WHERE LENGTH = '23'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 20) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur23_2
FROM #all_inits
WHERE LENGTH = '23'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 19) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur23_3
FROM #all_inits
WHERE LENGTH = '23'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 22) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur24_1
FROM #all_inits
WHERE LENGTH = '24'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 21) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur24_2
FROM #all_inits
WHERE LENGTH = '24'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 20) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur24_3
FROM #all_inits
WHERE LENGTH = '24'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 23) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur25_1
FROM #all_inits
WHERE LENGTH = '25'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 22) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur25_2
FROM #all_inits
WHERE LENGTH = '25'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 21) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur25_3
FROM #all_inits
WHERE LENGTH = '25'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 24) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur26_1
FROM #all_inits
WHERE LENGTH = '26'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 23) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur26_2
FROM #all_inits
WHERE LENGTH = '26'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 22) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur26_3
FROM #all_inits
WHERE LENGTH = '26'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 25) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur27_1
FROM #all_inits
WHERE LENGTH = '27'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 24) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur27_2
FROM #all_inits
WHERE LENGTH = '27'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 23) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur27_3
FROM #all_inits
WHERE LENGTH = '27'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 26) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur28_1
FROM #all_inits
WHERE LENGTH = '28'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 25) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur28_2
FROM #all_inits
WHERE LENGTH = '28'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 24) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur28_3
FROM #all_inits
WHERE LENGTH = '28'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 27) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur29_1
FROM #all_inits
WHERE LENGTH = '29'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 26) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur29_2
FROM #all_inits
WHERE LENGTH = '29'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 25) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur29_3
FROM #all_inits
WHERE LENGTH = '29'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 28) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur30_1
FROM #all_inits
WHERE LENGTH = '30'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 27) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur30_2
FROM #all_inits
WHERE LENGTH = '30'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 26) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur30_3
FROM #all_inits
WHERE LENGTH = '30'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 29) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur31_1
FROM #all_inits
WHERE LENGTH = '31'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 28) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur31_2
FROM #all_inits
WHERE LENGTH = '31'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 27) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur31_3
FROM #all_inits
WHERE LENGTH = '31'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 30) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur32_1
FROM #all_inits
WHERE LENGTH = '32'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 29) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur32_2
FROM #all_inits
WHERE LENGTH = '32'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 28) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur32_3
FROM #all_inits
WHERE LENGTH = '32'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 31) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur33_1
FROM #all_inits
WHERE LENGTH = '33'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 30) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur33_2
FROM #all_inits
WHERE LENGTH = '33'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 29) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur33_3
FROM #all_inits
WHERE LENGTH = '33'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 32) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur34_1
FROM #all_inits
WHERE LENGTH = '34'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 31) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur34_2
FROM #all_inits
WHERE LENGTH = '34'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 30) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur34_3
FROM #all_inits
WHERE LENGTH = '34'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 33) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur35_1
FROM #all_inits
WHERE LENGTH = '35'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 32) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur35_2
FROM #all_inits
WHERE LENGTH = '35'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 31) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur35_3
FROM #all_inits
WHERE LENGTH = '35'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 34) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur36_1
FROM #all_inits
WHERE LENGTH = '36'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 33) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur36_2
FROM #all_inits
WHERE LENGTH = '36'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 32) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur36_3
FROM #all_inits
WHERE LENGTH = '36'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 35) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur37_1
FROM #all_inits
WHERE LENGTH = '37'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 34) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur37_2
FROM #all_inits
WHERE LENGTH = '37'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 33) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur37_3
FROM #all_inits
WHERE LENGTH = '37'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 36) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur38_1
FROM #all_inits
WHERE LENGTH = '38'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 35) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur38_2
FROM #all_inits
WHERE LENGTH = '38'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 34) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur38_3
FROM #all_inits
WHERE LENGTH = '38'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 37) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur39_1
FROM #all_inits
WHERE LENGTH = '39'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 36) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur39_2
FROM #all_inits
WHERE LENGTH = '39'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 35) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur39_3
FROM #all_inits
WHERE LENGTH = '39'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 38) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur40_1
FROM #all_inits
WHERE LENGTH = '40'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 37) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur40_2
FROM #all_inits
WHERE LENGTH = '40'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 36) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur40_3
FROM #all_inits
WHERE LENGTH = '40'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 39) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur41_1
FROM #all_inits
WHERE LENGTH = '41'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 38) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur41_2
FROM #all_inits
WHERE LENGTH = '41'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 37) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur41_3
FROM #all_inits
WHERE LENGTH = '41'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 40) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur42_1
FROM #all_inits
WHERE LENGTH = '42'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 39) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur42_2
FROM #all_inits
WHERE LENGTH = '42'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 38) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur42_3
FROM #all_inits
WHERE LENGTH = '42'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 41) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur43_1
FROM #all_inits
WHERE LENGTH = '43'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 40) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur43_2
FROM #all_inits
WHERE LENGTH = '43'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 39) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur43_3
FROM #all_inits
WHERE LENGTH = '43'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 42) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur44_1
FROM #all_inits
WHERE LENGTH = '44'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 41) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur44_2
FROM #all_inits
WHERE LENGTH = '44'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 40) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur44_3
FROM #all_inits
WHERE LENGTH = '44'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 43) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur45_1
FROM #all_inits
WHERE LENGTH = '45'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 42) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur45_2
FROM #all_inits
WHERE LENGTH = '45'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 41) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur45_3
FROM #all_inits
WHERE LENGTH = '45'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 44) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur46_1
FROM #all_inits
WHERE LENGTH = '46'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 43) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur46_2
FROM #all_inits
WHERE LENGTH = '46'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 42) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur46_3
FROM #all_inits
WHERE LENGTH = '46'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 45) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur47_1
FROM #all_inits
WHERE LENGTH = '47'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 44) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur47_2
FROM #all_inits
WHERE LENGTH = '47'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 43) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur47_3
FROM #all_inits
WHERE LENGTH = '47'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 46) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur48_1
FROM #all_inits
WHERE LENGTH = '48'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 45) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur48_2
FROM #all_inits
WHERE LENGTH = '48'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 44) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur48_3
FROM #all_inits
WHERE LENGTH = '48'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 47) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur49_1
FROM #all_inits
WHERE LENGTH = '49'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 46) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur49_2
FROM #all_inits
WHERE LENGTH = '49'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 45) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur49_3
FROM #all_inits
WHERE LENGTH = '49'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 48) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur50_1
FROM #all_inits
WHERE LENGTH = '50'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 47) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur50_2
FROM #all_inits
WHERE LENGTH = '50'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 46) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur50_3
FROM #all_inits
WHERE LENGTH = '50'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 49) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur51_1
FROM #all_inits
WHERE LENGTH = '51'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 48) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur51_2
FROM #all_inits
WHERE LENGTH = '51'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 47) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur51_3
FROM #all_inits
WHERE LENGTH = '51'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 50) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur52_1
FROM #all_inits
WHERE LENGTH = '52'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 49) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur52_2
FROM #all_inits
WHERE LENGTH = '52'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 48) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur52_3
FROM #all_inits
WHERE LENGTH = '52'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 51) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur53_1
FROM #all_inits
WHERE LENGTH = '53'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 50) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur53_2
FROM #all_inits
WHERE LENGTH = '53'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 49) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur53_3
FROM #all_inits
WHERE LENGTH = '53'
	AND INITS_LENGTH = '3'

SELECT CODE,
	LEFT([NAME], 52) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur54_1
FROM #all_inits
WHERE LENGTH = '54'
	AND INITS_LENGTH = '1'

SELECT CODE,
	LEFT([NAME], 51) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur54_2
FROM #all_inits
WHERE LENGTH = '54'
	AND INITS_LENGTH = '2'

SELECT CODE,
	LEFT([NAME], 50) AS SURNAME,
	INITIALS,
	SPEC_CODE,
	SPEC_NAME
INTO #sur54_3
FROM #all_inits
WHERE LENGTH = '54'
	AND INITS_LENGTH = '3'

SELECT *
INTO #clin_split
FROM #sur10_1
UNION
SELECT *
FROM #sur10_2
UNION
SELECT *
FROM #sur10_3
UNION
SELECT *
FROM #sur11_1
UNION
SELECT *
FROM #sur11_2
UNION
SELECT *
FROM #sur11_3
UNION
SELECT *
FROM #sur12_1
UNION
SELECT *
FROM #sur12_2
UNION
SELECT *
FROM #sur12_3
UNION
SELECT *
FROM #sur13_1
UNION
SELECT *
FROM #sur13_2
UNION
SELECT *
FROM #sur13_3
UNION
SELECT *
FROM #sur14_1
UNION
SELECT *
FROM #sur14_2
UNION
SELECT *
FROM #sur14_3
UNION
SELECT *
FROM #sur15_1
UNION
SELECT *
FROM #sur15_2
UNION
SELECT *
FROM #sur15_3
UNION
SELECT *
FROM #sur16_1
UNION
SELECT *
FROM #sur16_2
UNION
SELECT *
FROM #sur16_3
UNION
SELECT *
FROM #sur17_1
UNION
SELECT *
FROM #sur17_2
UNION
SELECT *
FROM #sur17_3
UNION
SELECT *
FROM #sur18_1
UNION
SELECT *
FROM #sur18_2
UNION
SELECT *
FROM #sur18_3
UNION
SELECT *
FROM #sur19_1
UNION
SELECT *
FROM #sur19_2
UNION
SELECT *
FROM #sur19_3
UNION
SELECT *
FROM #sur20_1
UNION
SELECT *
FROM #sur20_2
UNION
SELECT *
FROM #sur20_3
UNION
SELECT *
FROM #sur21_1
UNION
SELECT *
FROM #sur21_2
UNION
SELECT *
FROM #sur21_3
UNION
SELECT *
FROM #sur22_1
UNION
SELECT *
FROM #sur22_2
UNION
SELECT *
FROM #sur22_3
UNION
SELECT *
FROM #sur23_1
UNION
SELECT *
FROM #sur23_2
UNION
SELECT *
FROM #sur23_3
UNION
SELECT *
FROM #sur24_1
UNION
SELECT *
FROM #sur24_2
UNION
SELECT *
FROM #sur24_3
UNION
SELECT *
FROM #sur25_1
UNION
SELECT *
FROM #sur25_2
UNION
SELECT *
FROM #sur25_3
UNION
SELECT *
FROM #sur26_1
UNION
SELECT *
FROM #sur26_2
UNION
SELECT *
FROM #sur26_3
UNION
SELECT *
FROM #sur27_1
UNION
SELECT *
FROM #sur27_2
UNION
SELECT *
FROM #sur27_3
UNION
SELECT *
FROM #sur28_1
UNION
SELECT *
FROM #sur28_2
UNION
SELECT *
FROM #sur28_3
UNION
SELECT *
FROM #sur29_1
UNION
SELECT *
FROM #sur29_2
UNION
SELECT *
FROM #sur29_3
UNION
SELECT *
FROM #sur3_1
UNION
SELECT *
FROM #sur30_1
UNION
SELECT *
FROM #sur30_2
UNION
SELECT *
FROM #sur30_3
UNION
SELECT *
FROM #sur31_1
UNION
SELECT *
FROM #sur31_2
UNION
SELECT *
FROM #sur31_3
UNION
SELECT *
FROM #sur32_1
UNION
SELECT *
FROM #sur32_2
UNION
SELECT *
FROM #sur32_3
UNION
SELECT *
FROM #sur33_1
UNION
SELECT *
FROM #sur33_2
UNION
SELECT *
FROM #sur33_3
UNION
SELECT *
FROM #sur34_1
UNION
SELECT *
FROM #sur34_2
UNION
SELECT *
FROM #sur34_3
UNION
SELECT *
FROM #sur35_1
UNION
SELECT *
FROM #sur35_2
UNION
SELECT *
FROM #sur35_3
UNION
SELECT *
FROM #sur36_1
UNION
SELECT *
FROM #sur36_2
UNION
SELECT *
FROM #sur36_3
UNION
SELECT *
FROM #sur37_1
UNION
SELECT *
FROM #sur37_2
UNION
SELECT *
FROM #sur37_3
UNION
SELECT *
FROM #sur38_1
UNION
SELECT *
FROM #sur38_2
UNION
SELECT *
FROM #sur38_3
UNION
SELECT *
FROM #sur39_1
UNION
SELECT *
FROM #sur39_2
UNION
SELECT *
FROM #sur39_3
UNION
SELECT *
FROM #sur4_1
UNION
SELECT *
FROM #sur4_2
UNION
SELECT *
FROM #sur40_1
UNION
SELECT *
FROM #sur40_2
UNION
SELECT *
FROM #sur40_3
UNION
SELECT *
FROM #sur41_1
UNION
SELECT *
FROM #sur41_2
UNION
SELECT *
FROM #sur41_3
UNION
SELECT *
FROM #sur42_1
UNION
SELECT *
FROM #sur42_2
UNION
SELECT *
FROM #sur43_1
UNION
SELECT *
FROM #sur43_2
UNION
SELECT *
FROM #sur43_3
UNION
SELECT *
FROM #sur43_3
UNION
SELECT *
FROM #sur44_1
UNION
SELECT *
FROM #sur44_2
UNION
SELECT *
FROM #sur44_3
UNION
SELECT *
FROM #sur45_1
UNION
SELECT *
FROM #sur45_2
UNION
SELECT *
FROM #sur45_3
UNION
SELECT *
FROM #sur46_1
UNION
SELECT *
FROM #sur46_2
UNION
SELECT *
FROM #sur46_3
UNION
SELECT *
FROM #sur47_1
UNION
SELECT *
FROM #sur47_2
UNION
SELECT *
FROM #sur47_3
UNION
SELECT *
FROM #sur48_1
UNION
SELECT *
FROM #sur48_2
UNION
SELECT *
FROM #sur48_3
UNION
SELECT *
FROM #sur49_1
UNION
SELECT *
FROM #sur49_2
UNION
SELECT *
FROM #sur49_3
UNION
SELECT *
FROM #sur5_1
UNION
SELECT *
FROM #sur5_2
UNION
SELECT *
FROM #sur5_3
UNION
SELECT *
FROM #sur50_1
UNION
SELECT *
FROM #sur50_2
UNION
SELECT *
FROM #sur50_3
UNION
SELECT *
FROM #sur51_1
UNION
SELECT *
FROM #sur51_2
UNION
SELECT *
FROM #sur51_3
UNION
SELECT *
FROM #sur52_1
UNION
SELECT *
FROM #sur52_2
UNION
SELECT *
FROM #sur52_3
UNION
SELECT *
FROM #sur53_1
UNION
SELECT *
FROM #sur53_2
UNION
SELECT *
FROM #sur53_3
UNION
SELECT *
FROM #sur54_1
UNION
SELECT *
FROM #sur54_2
UNION
SELECT *
FROM #sur54_3
UNION
SELECT *
FROM #sur6_1
UNION
SELECT *
FROM #sur6_2
UNION
SELECT *
FROM #sur6_3
UNION
SELECT *
FROM #sur7_1
UNION
SELECT *
FROM #sur7_2
UNION
SELECT *
FROM #sur7_3
UNION
SELECT *
FROM #sur8_1
UNION
SELECT *
FROM #sur8_2
UNION
SELECT *
FROM #sur8_3
UNION
SELECT *
FROM #sur9_1
UNION
SELECT *
FROM #sur9_2
UNION
SELECT *
FROM #sur9_3

« Prev

Page 1 | Page 2 | Page 3

Re: SQL Splits

2012-08-02 07:19 • by Nagesh (unregistered)

386185

Reply Quote

In our dev shop in Hyderabad this is know as NoSQL solution. Client look at SQL code and say "no" many times.

Re: SQL Splits

2012-08-02 07:42 • by Cujo (unregistered)

386186 in reply to 386087

Reply Quote

Precisely. I could see this SQL as a ONE TIME rilly quick and rilly dirty conversion to fix someone who put the full name in one field and is trying to move it to frist, muddle and lust naems. I'm betting that this is not the case.

(All misspellings deliberate for the spelling gestapo!)

I get code like this all the time and the chief reason I'm told was that it had to be done quickly. Those are the folks who get the code thrown back in their faces to fix on production systems when they eat up 80% of the system. "I put too much time in this to rewrite it from scratch, I can't even remember what it does!"

Re: SQL Splits

2012-08-02 07:53 • by biziclop

386187

Reply Quote

This post calls for a "bloody hell" button.

Re: SQL Splits

2012-08-02 07:54 • by havokk

386188

Reply Quote

Please, for the sake of the children, open a SQL training manual at the CASE expresion and then use it to paper-cut this idiot to death.

Re: SQL Splits

2012-08-02 08:07 • by spaceman (unregistered)

386189

Reply Quote

can someone please post a statement needed to create and seed the tables this statement uses. i have this strange compulsion to actually run this and see it in action.

Re: SQL Splits

2012-08-02 08:16 • by Mot (unregistered)

386190

Reply Quote

John Van Wyk
John Van III
John Van 3rd

Okay, 'Van' by itself is a rare last name but not unknown. There is NO WAY of figuring out from just the spaces which of the above is part of the last name and what is suffix.

I went through this exercise with a 80,000 name DB one time. I got everything to the right of the right-most space and compared it to a table of known values, and kept adding to that table from the examples in my table. (I got as far as "5th" but Henry 8th was dead by then, or at least didn't register with our company.)

I could do this without fear because I had a known universe of names. If I had to accept new names, it would not have worked.

Luckily the reason I did this exercise was to move to a new app that had a separate "name suffix" field.

Re: SQL Splits

2012-08-02 08:41 • by urgh (unregistered)

386191

Reply Quote

How can a company hire someone that codes like this? If your code contains 100 snippets of almost-identical stuff, it's the HUGE red flag that you are a total beginner that has never coded in his life.

Re: SQL Splits

2012-08-02 08:41 • by urgh (unregistered)

386192

Reply Quote

Re: SQL Splits

2012-08-02 08:41 • by urgh (unregistered)

386193

Reply Quote

Damn Parkinson.

Re: SQL Splits

2012-08-02 09:23 • by Ryan (unregistered)

386200 in reply to 386081

Reply Quote

iToad:
String processing with SQL. What could possibly go wrong?

Amen

Re: SQL Splits

2012-08-02 09:57 • by golddog (unregistered)

386216 in reply to 386149

Reply Quote

dogmatic:
Ouch! Even if you didn't know about BETWEEN you would at least google to see if something like that exists instead of writing this monstrosity.

Not really on topic, but one of my pet peeves with SQL (at least the variants with which I've worked) is that between is ordered.

Zero should be between 1 and -1 just as it's between -1 and 1. But no.

Re: SQL Splits

2012-08-02 12:38 • by Indeedly (unregistered)

386239 in reply to 386081

Reply Quote

I never comment here, but seeing this code made a void in my soul and I had to share it.

Re: SQL Splits

2012-08-02 13:23 • by lietu (unregistered)

386242

Reply Quote

So you're saying that's a glorified version of:

SELECT
SUBSTRING(name, 1, LOCATE(' ', name) - 1) AS name,
SUBSTRING(name, LOCATE(' ', name) + 1) AS suffix
FROM CLINICIAN;

?

Re: SQL Splits

2012-08-02 14:39 • by Old fart (unregistered)

386247

Reply Quote

You should post a warning label before exposing us unsuspecting people to such a cruel sample of code. That's just not right!

Re: SQL Splits

2012-08-02 17:20 • by Paul Neumann (unregistered)

386262 in reply to 386239

Reply Quote

Indeedly:
I never comment here, but seeing this code made a void in my soul and I had to share it.

You are not allowed to share void. It can only be accessed through the VoidFactory accessor class.

captcha: iusto - iusto think the captcha's were funny, not so much now.

Re: SQL Splits

2012-08-02 18:09 • by Joe (unregistered)

386271

Reply Quote

I think this SQL can be optimized.

Re: SQL Splits

2012-08-02 21:39 • by Mango Cream (unregistered)

386275

Reply Quote

This is why I hate string manipulations in SQL. (*A*)/

Re: SQL Splits

2012-08-03 01:44 • by TallMan (unregistered)

386283

Reply Quote

This must be someone paid based on LOC. And doing a pretty good job at it!
Making 2k lines out of something that should be 10. 200x higher wages than doing it properly!

Re: SQL Splits

2012-08-03 07:39 • by Quidam (unregistered)

386287 in reply to 386081

Reply Quote

String processing with SQL. It already went wrong.

Re: SQL Splits

2012-08-03 10:38 • by Ziplodocus (unregistered)

386323 in reply to 386131

Reply Quote

If that captcha is true, what is the definition of a cumquat?!

Re: SQL Splits

2012-08-03 16:02 • by mike (unregistered)

386373

Reply Quote

I think I know the guy that wrote this. It's that guy with the worn out ctrl, c, and v keys on his keyboard isn't it?

Re: SQL Splits

2012-08-03 18:08 • by Patrick (unregistered)

386381

Reply Quote

It goes on forever — and — oh my God — it's full of stars!

Re: SQL Splits

2012-08-03 20:29 • by Andrew (unregistered)

386382

Reply Quote

jeebus.... I know that is SQL server code but surely it could be done with instring or something.

here's it done in Oracle with regex

SQL> with t as (select 'HARTLEY JR' txt from dual)
2 select regexp_substr(txt,'^[^ ]+') first_string
3 ,regexp_substr(txt,'[^ ]+$') second_string
4 from t;

FIRST_S SE
------- --
HARTLEY JR

1 row selected.

Re: SQL Splits

2012-08-04 09:07 • by Philipp (unregistered)

386393

Reply Quote

I have never worked with SQL but I get a slight suspicion that this can be done more efficiently.

Re: SQL Splits

2012-08-05 05:20 • by David (unregistered)

386405 in reply to 386157

Reply Quote

hikari:
A page I am fond of: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

It's a page. Handling names is certainly filled with complex issues, most of which he touches on, but there's a certain point at which a programmer can blow off the concerns. You don't have a name or not one that's representable in Unicode? That's your problem. Likewise, that many programs have too short a length for names is arguable; that programmers seriously need to worry about unbounded names is not.

Re: SQL Splits

2012-08-06 05:28 • by jtf (unregistered)

386417

Reply Quote

Pure and simple design issue. Break out the individual columns in the target table and do the splits in the ETL code.

Re: SQL Splits

2012-08-07 04:58 • by Sander (unregistered)

386545

Reply Quote

That seriously looks like some awful generated SQL. Nobody could've typed that manually.

Re: SQL Splits

2012-08-07 13:52 • by roselan (unregistered)

386638

Reply Quote

Most indian coders are paid by the line.

That's a smart way to add a few bucks to your wage, if you are lucky enough to work for Infosys, Satyam or EDS... i guess.

Re: SQL Splits

2012-08-08 17:18 • by lonetaco (unregistered)

386766

Reply Quote

Wow. He really is a guru. You should study hard and learn from him.

Re: SQL Splits

2012-08-09 02:51 • by wkmanire (unregistered)

386781

Reply Quote

What...How.... dammit! Come on! At what point do you stop and consider that you may be doing it wrong!?

Re: SQL Splits

2012-08-09 14:25 • by Efficiency Expert (unregistered)

386834

Reply Quote

So this is what happens when SQL does the splits.

This is why I always recommend stretching.

Re: SQL Splits

2012-08-10 04:07 • by Jan (unregistered)

386871

Reply Quote

If all you have is a hammer, every problem looks like a nail.. and as an added bonus, you're a crap carpenter..

Couple of month's ago we had a tech-talk by a "xml-guru" boasting about xml databases: "There great, you can even build a webserver with them!"

Re: SQL Splits

2012-08-10 12:06 • by Dan (unregistered)

386955

Reply Quote

I almost just screamed out loud at work while scrolling through that.

Re: SQL Splits

2012-08-13 07:07 • by Brian (unregistered)

387023

Reply Quote

Finally a robust solution to splitting a string in T-SQL!

Re: SQL Splits

2012-08-13 15:11 • by PITA (unregistered)

387117

Reply Quote

Other than all of the carriage returns, I don't see any problems.

Re: SQL Splits

2012-08-15 04:58 • by TJ Powell (unregistered)

387266 in reply to 386081

Reply Quote

When I see these, I load up my briefcase - tell them I can't (won't) help them, and leave. Life is too short. I had one of these "opportunities" very recently, and I PASSED. Quickly. Makes my stomach hurt to look at it. Before I left the guy argued with me that I had written some SQL code that wasn't a join, since all of his were 8-60 lines long - and mine was 2 lines, and didn't have the word "join" in it. (It was a simple "where" clause on with a select on two tables.) He could not understand that there is more than one way to write a simple pivot "join" between two tables. lol

Re: SQL Splits

2012-08-15 05:00 • by TJ Powell (unregistered)

387267 in reply to 387266

Reply Quote

The answer to when you know is after 1.5 "screen" pages. If it's long than that, it's wrong.

Re: SQL Splits

2012-08-22 13:40 • by Conrad (unregistered)

387920 in reply to 386190

Reply Quote

Actually you can solve it. JosephStyons was able to do it with this answer to the StackOverflow question
How can I parse the first, middle and last name from a full name field in SQL?

Here's a SQL Fiddle that demonstrates it

Re: SQL Splits

2012-09-01 12:56 • by JoI hope this is a jopkee Celko (unregistered)

388845 in reply to 386081

Reply Quote

I hope this is a joke. There is nothing right in it; nothing!
At one consulting gig decdes ago, I suggested that we find all the code written by one guy, throw it out and start over without even bothering to read it. The client nodded.

I then suggested that we run over this guy in parking lot; the client and the other programmers all smiled and two of them got out their car keys.

Re: SQL Splits

2012-09-01 13:14 • by Joe Celko (unregistered)

388846 in reply to 386382

Reply Quote

I advise people to get a package like Melissa Data to clean up names and addresses. Life is too short to re-invent the wheel and to worry about "Dr. Jean-Paul van der Poon MD" gettign his mail.

Re: SQL Splits

2012-09-20 20:22 • by barfo rama (unregistered)

390556 in reply to 386074

Reply Quote

Some Jerk:
Warren:
If you even start writing this without thinking "there must be a better way", you're not a programmer, just a typist that knows SQL.

Correction... a typist who happens to know a website that displays sql code.

A secretary is a ho that can type.
An executive secretary is a typist that can screw.
A SQL programmer is a secretary who picks up tricks on Hollywood Blvd and squeals in delite.

captcha: appellatio

Re: SQL Splits

2012-09-20 20:30 • by barfo rama (unregistered)

390557 in reply to 386160

Reply Quote

Mick:
Evan:
Chronomium:
iToad:
String processing with SQL. What could possibly go wrong?
Indeed, this is clearly one of those cases where you're using a screwdriver to mix cake batter.

I've used pliers to eat a pancake before.

(It was a camping trip and we had a shortage of silverware because we're stupid, but I had my Leatherman.)
If your Leatherman doesn't have a fork in it, you should trade it in and get a Swiss Army Knife.

He used the fork to keep his eye from twitching after he saw this code.

Captcha: similis

« Prev

Page 1 | Page 2 | Page 3

	Sign On • Join • Forums
Hurry! Enter The Daily WTF's OMGWTF2 Contest by June 28th! - Prizes! Fame! Trophies! Do your worst: http://omg2.thedailywtf.com/

Comment On SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Re: SQL Splits

Add Comment

SUBMIT YOUR WTF

Content

Random Article

All Articles

Free WTF Swag!

Sponsored by

Side Bar WTF