May 29, 2012 at 8:37 am
Hello everyone
I hope that you all had a nice weekend.
I am working on cleaning up some data that I receive from an outside source. What?! Yes, I know.........
I am receiving one column with delimited data, I know, another real shocker. The data is City, State PostalCode all together in one column. I am not very good at performing text manipulations, so I need a little help with this.
The format of each is City, State PostalCode ie.... NEW LIBERTY, IA 52765-9420
I would like to know how to separate each into its respective column. I know how to perform an update, I need help with breaking the string apart so that I get City in the CityName Column, State into the StateAbbrev Column and Zip into PostalCode column
Thank You in advance for your help
Andrew SQLDBA
May 29, 2012 at 8:42 am
All done by J. Moden:
May 29, 2012 at 8:53 am
Do what?
I was hoping for something a little more simple that what is shown on that page.
Thanks
Andrew SQLDBA
May 29, 2012 at 9:06 am
AndrewSQLDBA (5/29/2012)
Do what?I was hoping for something a little more simple that what is shown on that page.
Thanks
Andrew SQLDBA
Do you want simple or fast? 😉
Technically, if you can use CLR then that would be simple and fast. But a lot of places are still unable to use them.
May 29, 2012 at 9:14 am
You mean that I cannot have both? 😀
Fast of course. I take it if I want fast I should follow what was first sent.
Andrew SQLDBA
May 29, 2012 at 9:21 am
AndrewSQLDBA (5/29/2012)
You mean that I cannot have both? 😀Fast of course. I take it if I want fast I should follow what was first sent.
Andrew SQLDBA
As of the last time I checked, Jeff's string splitter is the fastest way to split text (less than 8000 characters) using purely t-sql. A CLR function will beat it, just about, but it's not an option in a lot of development environments. Paul wrote a CLR here.
May 29, 2012 at 9:27 am
I think what is really making this more difficult for me is that the second delimiter is a blank space. I am not wrapping my head around that easily. There are only about 3000 rows, and this is a one time query.
Thanks
Andrew SQLDBA
May 29, 2012 at 9:29 am
No reason to use Jeff's splitter if the data format is constant:
use tempdb
go
declare @test-2 varchar(125)
SET @test-2 = 'barrington, il 60010-4020'
SELECT RIGHT(@test, 10) AS zip, SUBSTRING(@test, CHARINDEX(',',@test)+2, 2) AS state, SUBSTRING(@test, 1, CHARINDEX(',',@test)-1) AS city
Jared
CE - Microsoft
May 29, 2012 at 9:29 am
AndrewSQLDBA (5/29/2012)
I think what is really making this more difficult for me is that the second delimiter is a blank space. I am not wrapping my head around that easily. There are only about 3000 rows, and this is a one time query.Thanks
Andrew SQLDBA
So run a replace on the first deliminator + second deliminator to equal one deliminator 😀
Then use Jeff's little bit of work.
You could use an XML splitter, but it will be slower.
May 29, 2012 at 9:35 am
Thank You to Everyone for all the great advice. Always learn something when I post here.
Have a good one
Andrew SQLDBA
May 29, 2012 at 9:43 am
SQLKnowItAll (5/29/2012)
No reason to use Jeff's splitter if the data format is constant:
use tempdbgo
declare @test-2 varchar(125)
SET @test-2 = 'barrington, il 60010-4020'
SELECT RIGHT(@test, 10) AS zip, SUBSTRING(@test, CHARINDEX(',',@test)+2, 2) AS state, SUBSTRING(@test, 1, CHARINDEX(',',@test)-1) AS city
Very true, if you know the number of elements never changes this will work. I'd be interested to see a performance test vs Jeff's code on say 250 elements.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply