SQL 2005 - Parse CSV field
Posted on 2007-08-05
I have a field (f2) in a tblImportError that has names, with no spaces, seperated by commas (last,first,middle)
This code works perfectly, until we come to a name that has a first name with one character and no middle name. It will error out.
Changing the SUBSTRING(REVERSE(f2), 2, 1) to SUBSTRING(REVERSE(f2), 1, 1) in the key2 line leaves commas after the last name.
Set key2 = SUBSTRING(f2, CHARINDEX(',', f2) + 1, LEN(f2) - (CHARINDEX(',', f2) + 0) - CASE WHEN
SUBSTRING(REVERSE(f2), 2, 1) = ',' THEN 1 ELSE 0 END) -- firstname
,key3 = LEFT(f2, CHARINDEX(',', f2) - 1) -- lastname
ON tblImportError.key1 = tblImport1.f1
Any help is appreciated!!!