asked on

tokenizing strings in sql

Hi,

I have data in an 'Address' column which needs to be migrated to new table whose
columns are now 'Address1', 'Address2', 'Address3' and 'Address4'.

could anyone help me write an sql query that can cut up a string into four component strings.
The cut token would be a new line character with any whitespace character(including other newline) to the left or right.

ie
Address =

"Jon smith
Melbourne University
Australia"

would be cut up into
address1="Jon Smith"
address2="Melbourne Unversity"
address3="australia"
address4=<null>

ie
Address =
"Henry Smith

Economics Faculty

Melbourne University
Victoria
Australia.
Earth."

would be cup into
Address1="Henry Smith"
address2="Economics Faculty"
Address3="Melbourne University"
Address4=
"Victoria
Australia.
Earth"

Any help on this would be much appreciated

ASKER CERTIFIED SOLUTION

ewahner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ksbhat

See this

http://www.devx.com/tips/Tip/20009

ewahner

Mine is more efficient as it uses the charindex to move to the next delimiter, whereas the devx one loops thru each character...very inefficient. Also you can take out the if and just do the insert:

-- if not exists( select top 1 arrValue from @tmpTable where arrValue = @data )
-- begin
insert into @tmpTable ( arrValue ) values ( @data )
-- end

If you don't care about non-unique values.