SQL Server Performance Forum – Threads Archive

break column

Hi I have colum named CSZ for city state and zipcode i want to select from this column three diferent column one for city then state and then zip the data in the file is listed like this Atlant GA, 48053 How can i do this Thanks Thanks!!

Is this in the design stage, or is the system already in use? If you’re still in design, then have three separate columns for City, State and Zipcode. If already in use, then you’re out of luck. Also, if the data says "Atlant GA" then you also have a problem with data quality.

Try the following:
This assumes that your City has no spaces and the format is as you mentioned it to be CREATE TABLE MyTable (CSZ VARCHAR(100))
go INSERT INTO MyTable VALUES (‘Atlant GA, 48053’)
INSERT INTO MyTable VALUES (‘LosAngeles CA, 90210’)
go CREATE FUNCTION dbo.CityStateZip
(@IDENTIFIER VARCHAR(10), @CSZ varchar(200))
RETURNS varchar(200) AS BEGIN DECLARE @City VARCHAR(85)
DECLARE @State VARCHAR(3)
DECLARE @Zip VARCHAR (10) SET @City = SUBSTRING(@CSZ,1,CHARINDEX(‘ ‘,@CSZ,1)-1)
SET @State = SUBSTRING(@CSZ,CHARINDEX(‘ ‘,@CSZ,1),LEN(@CSZ))
SET @Zip = SUBSTRING(@CSZ,CHARINDEX(‘ ‘,@CSZ,1)+5,LEN(@CSZ)) IF @IDENTIFIER = ‘City’
BEGIN
SET @CSZ = @City
END
IF @IDENTIFIER = ‘State’
BEGIN
SET @CSZ = @State
END
IF @IDENTIFIER = ‘Zip’
BEGIN
SET @CSZ = @Zip
END RETURN @CSZ END
go SELECT dbo.CityStateZip(‘City’, CSZ) AS City, dbo.CityStateZip(‘State’, CSZ) AS State, dbo.CityStateZip(‘Zip’, CSZ) AS Zip From MyTable
go
DROP FUNCTION CityStateZip
go DROP TABLE MyTable
go
– Tahsin

Read about Normalization
http://www.datamodel.org/NormalizationRules.html Also read this to split it into seperate columns
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14124 Madhivanan Failing to plan is Planning to fail

You know, sometimes PARSENAME() comes in really handy. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Using Tahsins’ DDL (and also assuming that there are no spaces in the city name). <pre id="code"> SELECT PARSENAME(REPLACE(REPLACE(CSZ,’,’,”), ‘ ‘, ‘.’),3) AS City , PARSENAME(REPLACE(REPLACE(CSZ,’,’,”), ‘ ‘, ‘.’),2) AS State , PARSENAME(REPLACE(REPLACE(CSZ,’,’,”), ‘ ‘, ‘.’),1) AS ZIP FROM MyTable </pre id="code"> I hope, you need this in order to properly normalise your table structure. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

>>You know, sometimes PARSENAME() comes in really handy Yes I know. But the problem is if CSV has more than four words, then PARSENAME() will generate NULL values and you need to split it further to work on [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Hi all, <blockquote id="quote">quote:<hr height="1" noshade id="quote">assuming that there are no spaces in the city name<hr height="1" noshade id="quote"></blockquote id="quote">That’s a bit of an assumption! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Anyway, here’s a further way… <pre id="code">–data declare @t table (csz varchar(60)) insert @t (csz) values (‘Some City GA, 48053’) –calculation select left(csz, charindex(‘,’, csz) – 4) as city, substring(csz, charindex(‘,’, csz) – 2, 2) as state, substring(csz, charindex(‘,’, csz) + 2, 60) as zip from @t</pre id="code">Assuming that there are no commas in the city name (if there are, the reverse function will probably have to come into play) [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Oh, and here’s a very similar request (so you know you’re not the only one)… http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63847</a> Ryan Randall www.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that’s the hard part.

]]>