SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


split a string


split a string

Author
Message
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3913 Visits: 4025
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19122
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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7475 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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3913 Visits: 4025
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 Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 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 Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88520 Visits: 41130
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3913 Visits: 4025
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