I have an address field in a table that contains one, two, three or more lines of an address.
I need to split this into three fields for later insertion into CSV.
I have done something similar with the name field which I don't lile because I am testing the same condition twice
- CASE WHEN CHARINDEX(' ',RTRIM(LTRIM(full_name))) > 0 THEN
-
RTRIM(LTRIM(SUBSTRING(full_name,CHARINDEX(' ',full_name)+1,20)))
-
ELSE RTRIM(LTRIM(full_name))
-
END surname,
-
CASE WHEN CHARINDEX(' ',RTRIM(LTRIM(full_name))) > 0 THEN
-
RTRIM(LTRIM(SUBSTRING(full_name,1,CHARINDEX(' ',full_name)-1)))
-
ELSE RTRIM(LTRIM(initials))
-
END forename,
but the delimiter here was always space.
The address field has carriage return, newline and possibly tab.
How do I split the string where any of these delimiters occur?
(The delimitter is showing as an empty square)
