Separate results in column from query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Separate results in column from query

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-07-24 : 11:22:51

I have a query that I am running that returns the city, state and zip all in one column. I need to separate them out. Here is an example of how they come over:

Columbus, OH 43213

City is separated by a comma and the state and zip have a space in between them. Thanks for the help in advance, I'm a novice to SQL sever.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-24 : 11:32:28

You can split it like shown below IF the data is clean and exactly as you specified. But, it will fail (for example if you have a 5+4 zip code, or if there is no comma or if there is more than one comma etc.)

DECLARE @x VARCHAR(32) = 'Columbus, OH 43213';
SELECT 
	STUFF(@x,CHARINDEX(',',@x),LEN(@x),'') City,
	LEFT(LTRIM(STUFF(@x,1,CHARINDEX(',',@x),'')),2) State,
	RIGHT(RTRIM(@x),5) Zip;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 11:38:29

First question is whether format is consistent. Otherwise you might have break it up into groups based on format and apply different logic for each group based on its format.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-07-25 : 10:59:35

The data is the same city, state (OH) zip (5 digits). Thanks for the feedback. I will run what you posted and see what I get.
Thanks

jpost
Starting Member

43 Posts

Posted - 2012-07-25 : 11:01:38

Forget to mention that the city is not all named Columbus. So what would I need to do for the declare statement, if anything? I have a total of 6 different cities all varied in length.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 11:08:58

quote:
Originally posted by jpost

Forget to mention that the city is not all named Columbus. So what would I need to do for the declare statement, if anything? I have a total of 6 different cities all varied in length.

that was just for illustration

you dont need DECLARE variable part at all

just use logic explained in thread and replace variable with your table columnname


SELECT 
	STUFF(column,CHARINDEX(',',column),LEN(column),'') City,
	LEFT(LTRIM(STUFF(column,1,CHARINDEX(',',column),'')),2) State,
	RIGHT(RTRIM(column),5) Zip
FROM YourTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-07-25 : 11:11:16

Thanks

jpost
Starting Member

43 Posts

Posted - 2012-07-26 : 07:39:04

So here is my code, I get an error about incorrect syntax around line 9 near ',', I can't seem to figure it out. Also I put the above statement in a sub select, hopefully this will work as well
select
s.studentNumber,
s.lastName,
s.firstName,
s.schoolID,
s.grade,
convert(varchar,s.birthdate,101)AS 'birthDate',
s.activeYear,
(SELECT addressLine2(column,CHARINDEX(',',c),LEN(column),'') City,
LEFT(LTRIM(STUFF(column,1,CHARINDEX(',',column),'')),2) State,
RIGHT(RTRIM(column),5) Zip
from v_MailingAddress ma)
from student s
INNER JOIN school sc on sc.schoolID = s.schoolID
INNER JOIN v_MailingAddress ma ON ma.personID = s.personID
--WHERE s.activeYear = 1
order by s.studentNumber

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-26 : 07:44:27

In Visakh's post, wherever he showed "column" you should replace it with addressLine2 - so this is what you need:

SELECT
	s.studentNumber,
	s.lastName,
	s.firstName,
	s.schoolID,
	s.grade,
	CONVERT(VARCHAR, s.birthdate, 101) AS 'birthDate',
	s.activeYear,
	STUFF(ma.addressLine2,CHARINDEX(',',ma.addressLine2),LEN(ma.addressLine2),'') City,
	LEFT(LTRIM(STUFF(ma.addressLine2,1,CHARINDEX(',',ma.addressLine2),'')),2) State,
	RIGHT(RTRIM(ma.addressLine2),5) Zip
FROM
	student s
	INNER JOIN school sc
		ON  sc.schoolID = s.schoolID
	INNER JOIN v_MailingAddress ma
		ON  ma.personID = s.personID
		    --WHERE s.activeYear = 1
ORDER BY
	s.studentNumber

jpost
Starting Member

43 Posts

Posted - 2012-07-26 : 07:57:33

Thanks did exactly what I wanted it to do. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources