By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,143 Members | 828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,143 IT Pros & Developers. It's quick & easy.

Split one field string with a delimitter into multiple fields

code green
Expert 100+
P: 1,726
I am migrating data from DB1 to DB2 via csv.
In DB1 there is only one address field 'address' that contains carriage returns between each address line.
DB2 however has three fields for the address going into the csv as Addr-01, Addr-02, Addr-03
How do I split this one field into three fields?
It has to be three even though DB1 address may have four or five 'lines' so any extra will just have to be concatenated into address3.

This is roughly the query
Expand|Select|Wrap|Line Numbers
  1. SELECT customer 'Account',
  2. full_name Name, address 'Addr-01',
  3.     --Addr-02,
  4.     --Addr-03,
  5. city 'Town',
  6. county 'County',
  7. postcode 'Post Code' FROM customer
I wrote a function that returned a table before I found it had to be used in a JOIN which meant a record for each address line. This is the function
FUNCTION splitString (@delim CHAR(1), @str VARCHAR (100))
RETURNS @t TABLE (line VARCHAR(50))
AS
BEGIN
DECLARE @pos INT
DECLARE @piece VARCHAR(50)

IF RIGHT(RTRIM(@str),1) <> @delim
SET @str = @str + @delim

SET @pos = CHARINDEX(@delim, @str)
WHILE @pos <> 0
BEGIN
SET @piece = LEFT(@str, @pos - 1)

INSERT INTO @t VALUES(CAST(@piece AS VARCHAR(50)))

SET @str = STUFF(@str, 1, @pos, '')
SET @pos = CHARINDEX(@delim , @str)
END
RETURN
END
Apr 1 '10 #1
Share this Question
Share on Google+
1 Reply

Uncle Dickie
P: 67
The function below may get what you are after, it works on a very limited test I have done. There is no error trapping in the function so if there are not enough carriage returns (i.e. at least 2) it may have a fit!:

Expand|Select|Wrap|Line Numbers
  1. FUNCTION MySplit(@Line int, @str nvarchar(255))
  2. RETURNS nvarchar(255)
  3.  
  4. AS
  5. BEGIN
  6.  
  7. DECLARE    @Output nvarchar(255)
  8.  
  9. SET @Output = CASE    @Line
  10.                 WHEN 1 THEN    substring(@Str,1,CHARINDEX(char(13), @str))
  11.                 WHEN 2 THEN substring(@Str,CHARINDEX(char(13), @str)+1,charindex(char(13),(substring(@str,charindex(char(13),@str)+1,255))))
  12.                 WHEN 3 THEN substring(@Str,
  13.                         len(substring(@Str,1,CHARINDEX(char(13), @str)))
  14.                         + 2 +
  15.                         len(substring(@Str,CHARINDEX(char(13), @str)+1,charindex(char(13),(substring(@str,charindex(char(13),@str)+1,255))))),
  16.                         255)
  17.                 ELSE    'Not defined'
  18.             END
  19.  
  20. RETURN @Output
  21. END
you would then need to call the function 3 times passing the line variable you are after:

Expand|Select|Wrap|Line Numbers
  1. SELECT   MySplit(1,Address1) AddressLine1
  2.         ,MySplit(2,Address1) AddressLine2
  3.         ,MySplit(3,Address1) AddressLine3
  4.         ,MySplit(4,Address1) JustTest
  5.         ,Address1
  6. FROM        MyTable
Jun 2 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.