.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Help in writing a funcion in Splitting Address field into City,state and zip in SQL

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

Hi All,

I have to split a column into  city/state/zip.
MACON GA 31220
ORLANDO, FL. 32817


city state zip

MACON GA 31220


I tried 2 cases:

declare @s varchar(100)
select @s = 'Los Angeles, CA 90022'
select charindex(',',@s)
CITY = substring(@s,0,charindex(',',@s))
,STATE = substring(@s,charindex(',',@s)+1,len(@s)- (charindex(',',@s)+1 + 5))
,"ZIP CODE " = RIGHT(@s,5)

declare @s varchar(100)
select @s = 'Los Angeles. CA 90022'
select charindex('.',@s)
CITY = substring(@s,0,charindex('.',@s))
,STATE = substring(@s,charindex('.',@s)+1,len(@s)- (charindex('.',@s)+1 + 5))
,"ZIP CODE " = RIGHT(@s,5)


Thanks ,


View Complete Post

More Related Resource Links

How to extract City State Zip using Regex match

Hi there - I am parsing a file which contains customer address in the following 2 formats:   Format #1 12345 Melrose Place New York NY USA 12987     Format # 2: 12345 Melrose Place New York NY 12987   I need to put the data into Address, City, State and Zip fields. I am able to parse and put the data (specifically line 2) in the fields for format #1 but am having issues doing the same for format # 2 because format # 2 doesn't have USA as a reference point. Below is my code if any expert can help that will be appreciated Dim AddressChunk As String = tokenizer.NextToken() If AddressChunk.Contains("USA") Then _State = AddressChunk.Substring(AddressChunk.IndexOf("USA") - 4, 2).Trim _City = AddressChunk.Substring(0, AddressChunk.IndexOf("USA") - 4).Trim _Zip = Regex.Match(AddressChunk, "\d{5}").Value Else _Zip = Regex.Match(AddressChunk, "\d{5}").Value _State = AddressChunk.Substring(Regex.Match(AddressChunk, "\s[a-zA-Z]{2}\s\d{5}").Value - 5).Trim _City = End If

Splitting a memo field into lines and adding a value at the start of each line

I have one memo field which has a bunch of text in it. I want to update another memo field from the first one but i want to divide it into lines of 50 characters and put a carriage return at the end of each line and a string value (Note:) at the beginning of each line But I am not sure how to go about it. The text in the first field may have line breaks and carriage returns or it may not. So is there a way i can strip out anything like that first See example below Memo Field1 The quick brown fox jumped over the lazy dog. And the Dish ran away with the spoon, the little dog laughed to see such fun and the Cow fell of the wall Memo Field2 Note: The quick brown fox jumped over the lazy dog. And Note: the Dish ran away with the spoon, the little dog l Note: aughed to see such fun and the Cow fell of the wal Note: l   ideally it wouldn't cut words in half but I think I may be asking too much.   Is this possible?

Writing to excel file - The field is too small to accept the amount of



I am writing to excel file using jet driver. If the length of value exceeds 255 characters i get the below error.

'The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data'

Please could you provide a solution to how can i add values with length greater than 255. Excel file is 97-2003 format.

System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
 System.Data.OleDb.OleDbConnection connection =
          new System.Data.OleDb.OleDbConnection(
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destinationFilePath + ";Extended Properties=Excel 8.0;");
command.CommandText = "Insert into [sheet1$] (col1, col2) values ('1', 'TEXT Message greater than 255 characters')

The pager address field for an operator is too short

The number of pager addresses I need to add to an operator exceeds the fields limit of 100 characters. Does anyone know a work around?

What is the difference between view state and a hidden field


What is the difference between view state and a hidden field?

As we know that view state allows the the state of objects to be stored in hidden fields and is stored in client side. but what is the exact difffrence between a view state and a hidden field

Truly Understanding View State

This article provides a detailed understanding of the ViewState.

IP Address Blocking or Restriction in SQL Server

"is it possible to block connections to SQL Server based on IP address". As far as now there is no official way in SQL Server to block the connections in SQL Server based on IP address. However this can be done from the OS end, we have the following three options available, refer HERE for more.

.RRAS IP Filter

Writing custom MembershipProvider

ASP.NET Membership mechanism allows to use the build-in server tables to store and retrieve user data. By default, this mechanism can only be used with SQL Server database to which Microsoft provides suitable MembershipProvider class.
Inheriting from this abstract class, developer can use his own table(-es) from any database and use any database engine other than SQL Server, like Oracle, DB2 etc.

10 Tips for Writing High-Performance Web Applications

Writing a Web application with ASP.NET is unbelievably easy. So easy, many developers don't take the time to structure their applications for great performance. In this article, I'm going to present 10 tips for writing high-performance Web apps. I'm not limiting my comments to ASP.NET applications because they are just one subset of Web applications. This article won't be the definitive guide for performance-tuning Web applications-an entire book could easily be devoted to that. Instead, think of this as a good place to start.

my ip address

IP Tejji answers question like what is my ip address? find my ip addresss? detect my ip address? Best part is it also determines your local ip address as well as mac address. Further if you keen on browser specification and details regarding browser capabilities can also be found. It also help you see your geographic location. Other details that are available are Latitude, Longitude, timezone, country, region & city. In short it is one-stop-shop for very rich details about your IP and related information.

Design Patterns - Using the State Pattern in C#

What is the State Pattern?

The State Pattern is a behavioral pattern that can be used to alter the behavior of an object at run time. As the state of an object changes, the functionality of the object can change drastically. This change of behavior is hidden from the Client and the Client interfaces with a wrapper object known as the Context. The State Pattern is a dynamic version of the Strategy Pattern.

Writing Custom Web Parts for SharePoint 2007

As I mentioned in an earlier blog post, SharePoint 2007 is built on top of ASP.NET 2.0, which means you can now use ASP.NET 2.0 features (Forms Authentication, Master Pages, Membership, Site Navigation, New Data Controls, etc) when building SharePoint sites. This is true for both the new Windows SharePoint Services 3.0 version (which will be a free download) as well as Microsoft Office SharePoint Server 2007 (which costs money).

Reading and Writing Images From a Windows Mobile Database using UltraLite 10(C#)

Periodically I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store and image in a database? Well in an environment where you synchronizing data between a local mobile database and a consolidated (central) database this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database this chart could be sent down to the doctor's device once it became available.

For this article I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink

XML Reading and Writing

We learned to process XML files using the Document Object Model as implemented by the XmlDocument class. To go further and make XML friendlier, the .NET Framework provides many other classes for different purposes, allowing you to create and manage nodes from custom .NET classes.
Besides the XmlDocument and the derived classes of XmlNode, the .NET Framework provides the XmlTextWriter class, which is derived from XmlWriter. The XmlTextWriter class works in a top-down approach to create, or deal with, the contents of an XML file. This class writes an XML node and moves down without referring

Writing Http Handlers and Modules in IIS 7.5

This article describes how Http modules and handlers are to be written and configured in IIS 7.5. The concept of a handler and module remains same, so dose there programming model. In this section we start off with the concept of a Http module and a Http handler, then talk about how to code them and finally describe the two methods available to configure them.

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

view state vs request parameters


If  the view state is the data entered into the form fields then they are supposed to be available in the request parameters or request body. Then why would we need view state for?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend