split a string

Question

Post reply

split a string

sqlfriend

SSC Guru

Points: 52781
More actions
July 3, 2013 at 1:19 pm

#395083

If I have a street address that I would like to split street from Apt number, how could I do that?
for example now I have fullstreet ='100 NW 25 St APT# 303'
Now I would like to split fullstreet into two columns Street = '100 NW 25 St ' and Apartment = #303
what is the syntax?
Thanks much

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this?

DECLARE @fullstreet varchar(50) ='100 NW 25 St APT# 303'

SELECT LEFT( @fullstreet, PATINDEX('%APT%', @fullstreet)),

REPLACE( RIGHT( @fullstreet, 1 + LEN( @fullstreet) - PATINDEX('%APT%', @fullstreet)), 'APT# ', 'Apartment = #')

Depends greatly on your data. A simple example can be misleading.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

sqlfriends (7/3/2013)
If I have a street address that I would like to split street from Apt number, how could I do that?
for example now I have fullstreet ='100 NW 25 St APT# 303'
Now I would like to split fullstreet into two columns Street = '100 NW 25 St ' and Apartment = #303
what is the syntax?
Thanks much

Looks like a street address in Miami, FL. Unfortunately there's many variants on the way an apartment number will be represented. However if I recall my postal science correctly, the apartment number will usually be in the last portion of the street address and will usually be separated by a blank or a #.

Try using RIGHT and then finding the location of the first non-alphanumeric character, remembering that an apartment number could be something like H210.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Checked our data, if there is an apartment it will have apt#, so your query will work for me, will do more test, thanks much

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

Parsing addresses (and names) is perhaps one of the trickiest tasks in SQL. There's really no sure fire way to take a name or address that was originally placed in a single column and parse it 100% correctly considering the almost infinite variations. In my experience it's best in the long run to just split the entire string into individual columns then go about looking for exceptions. So with your address example, I'd split it (all the addresses in the table) into:

[USPS Address Delivery Line Components w/periods as spaces just used here for padding)

primaryAddressNumber preDirectional streetName suffix postDirectional secondaryAddressIndentifier secondaryAddress

================ =========== ======== ==== =========== ===================== =============

100..........................NW.................25..............St......[space]............APT#.............................303

Now if your address list is anything close to standard, this will cover the majority of the addresses, but you will then have to laboriously query the results to weed out and fix the data. The example above doesn't even address other things like PO Boxes, military APOs, Rural Route Numbers, etc.

So what I'd do FIRST is split all the address elements of each individual address into separate columns, then start grouping them by type of address (standard, military, PO Boxes, etc.) Your best resource after that is USPS.gov.

Now, to do the split, here's a function that I use for doing splits on spaces or nulls. It's basically the classic DelimitedSplit8K function with the delimiter hard-coded to be a space rather than any particular character.

[EDIT: My first post was of an obsolete version of the splitter. I've update the code below to the inline tvf version. Sorry for any inconvenience or for giving Jeff heartburn.]

WITH cteAddress(id,addr) AS

(

SELECT 1,'100 NW 25 St APT# 303' UNION ALL

SELECT 2,'10 SW 125 Ave' UNION ALL

SELECT 3,'234 Pine Ln Suite 303' UNION ALL

SELECT 4,'987-100 E Maple Ave' UNION ALL

SELECT 5,'345 3rd St Bungalow #9'

)

SELECT

a.id,

dsk.ItemNumber,

dsk.Item,

a.addr

FROM

cteAddress a

CROSS APPLY

dbo.itvfDelimitedSplit8K_NULLS(a.addr) dsk

CREATE FUNCTION [dbo].[itvfDelimitedSplit8K_NULLS]

(

@pString VARCHAR(8000)

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(

WITH

E1(N) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

E2(N) AS

(

SELECT 1 FROM E1 a, E1 b

),

E4(N) AS

(

SELECT 1 FROM E2 a, E2 b

),

cteTally(N) AS

(

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS

(

SELECT t.N+1

FROM cteTally t

WHERE (SUBSTRING(@pString,t.N,1) = ' ' OR t.N = 0)

)

SELECT

ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),

Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(' ',@pString,s.N1),0)-s.N1,8000))

FROM

cteStart s

)

GO

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

To add to what Steven has already posted, there's really no need to redevelop the wheel. Buy a "CASS Certification" program that will do this for you. It'll be a heck of a lot cheaper than you trying to write something on your own and it'll also be a whole lot more accurate. It'll also gen an exception list for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Thanks Steve and Jeff.

I don't know if we can buy some tool now for this split. probably not at this time.

This looks quite complicated about spliting, I will look at Steve 's code first.