CODEWITHSUNDEEP
sqlsql-server-2008splitsubstringcharindex
user3610494
user3610494

Reputation: 1

How Do I Split String In SQL Server Based On String Length

I want to copy my old data to new DB. i have a address column in my old db with datatype ntext, but in new DB i must use 3 column to put address (address1, address2, address3 with datatype nvarchar(60))

How can i split the string in my OLD DB to my NEW DB?

ex: query from old db : 

`select address from client`

result : 

Jl. Taman Crystal 2 Blok TC 2 No.79 RT.001/018, Cluster Crystal Residence, Summarecon Serpong, Pakulonan Barat - Kelapa Dua

Upvotes: 0

Views: 94

Answers (1)

Hogan
Hogan

Reputation: 70529

It really depends on how much data you need to process.

For small sets (say less than 10K or so) I'd do it like this:

  1. Create a staging table with space for a key the old address and the 3 new address.
  2. Copy the addresses into the staging table.

  3. Write an "extraction" query for the different parts and run each of them one by one. For example to get the address1 column you could use the left function up to location of ,

    INSERT INTO STAGE (address1)
SELECT LEFT(address,location(address,','))

Once you have done each of these ad-hoc queries, you can run some queries on the result and make sure you hare happy with it. If you have problems modify your query and run it again.

Once you are happy with STAGE copy it to your final table.

For larger sets I would write an program with good error checking, reporting and exception tables etc. A real parser and ETL. Not worth it for a small amount of data you can look at and work with by hand.

Upvotes: 1

Related Questions