Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

split a string Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 1:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:41 PM
Points: 1,770, Visits: 3,202
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

Post #1470206
Posted Wednesday, July 3, 2013 1:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:29 PM
Points: 3,513, Visits: 7,565
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1470208
Posted Wednesday, July 3, 2013 7:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1470282
Posted Wednesday, July 3, 2013 8:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:41 PM
Points: 1,770, Visits: 3,202
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
Post #1470286
Posted Wednesday, July 3, 2013 8:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 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


 
Post #1470288
Posted Thursday, July 4, 2013 11:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470488
Posted Monday, July 22, 2013 5:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:41 PM
Points: 1,770, Visits: 3,202
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.
Post #1476313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse