Parse Field - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Parse Field

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-03-30 : 18:14:10

I want to parse a column into multiple columns:

For example:

7887 Bishop St.

I want one column saying

7887

and a second column saying

Bishop

Any suggestions:

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 18:33:43

You'll need to be a bit more specific because this code will work in the case you gave but probably won't work on all your data.

declare @string varchar(100)
set @string = '7887 Bishop St.'
select left(@string, charindex(' ', @string) - 1),
    substring(@string, 
        charindex(' ', @string) + 1, 
        charindex(' ', @string, charindex(' ', @string) + 1) - charindex(' ', @string) - 1)

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-03-30 : 18:44:06

Thank YOu,

This is a very good start:

Here are some other examples:

28 Apple Blvd

784 Kenan St.

1001 Townsend Drive

The result set should look somewhat like this:

Column_1 Column_2
-------- -------
28 Apple
784 Kenan
1001 Townsend

Once again, thanks for you rehlp

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-03-30 : 18:46:26

Sorry, the formatting on that looked bad. Here is how it should be.

Sample Data:

28 Apple Blvd
784 Kenan St.
1001 Townsend Drive

Expected Output:

Column1
28
784
1001

Column2
Apple
Kenan
Townsend

Thanks . . .

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-30 : 20:51:01

[code]
select col, dbo.fnParseString(-1, ' ', col), dbo.fnParseString(-2, ' ', col)
from
(
select col = '28 Apple Blvd' union all
select col = '784 Kenan St.' union all
select col = '1001 Townsend Drive'
)a
[/code]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-03-30 : 21:13:18

Thanks to everyone for their input.

Once again the output of the query would be:

Column 1 Column 2
-------- --------

23 Apple
784 Kenan
1001 Townsend

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-30 : 21:37:37

quote:
Originally posted by oahu9872

Thanks to everyone for their input.

Once again the output of the query would be:

Column 1 Column 2
-------- --------

23 Apple
784 Kenan
1001 Townsend

How do you get Column1 = 23 for row no 1 ?

And what's wrong with my query ?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-02 : 11:10:59

khtan I think that the 23 is the house number as it appears to be a address that you are trying to divide into different columns.

am i correct? if so have you not tried the substring function as above this will work fine

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-02 : 11:45:22

Yes, but the source data says 28

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-02 : 12:24:31

quote:
Originally posted by X002548

Yes, but the source data says 28

The sql statement is supposed to check the address on-line, find out that no house #28 exists, and return the nearest match, #25. Come on, Brett, isn't it obvious!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources