Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


split a string


split a string

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8569 Visits: 18148
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.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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
Steven Willis
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 1721
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




 
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45313 Visits: 39936
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 3863
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search