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 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