By: Aubrey Love | Updated: 2020-02-19 | Comments (11) | Related: More >T-SQL

We needto split the address into separate columns. There are 3 steps in the Text to Columns function:- Select the column A. Go to the “Data” tab, from the “Data Tools” group, click on “Text to Columns”. Select the column that you want to split; From the Data ribbon, select “Text to Columns” (in the Data Tools group). This will open the Convert Text to Columns wizard. Here you’ll see an option that allows you to set how you want the data in the selected cells to be delimited. Make sure this option is selected. The syntax for the Split function in MS Access is: Split ( expression ,delimiter ,limit ,compare ) Parameters or Arguments expression The string to split into substrings based on a delimiter. Delimiter Optional. The delimiter used to split expression into substrings. If not specified, the delimiter will default to a space character. Delimited text is text that has some character, such as a comma, tab, or space, separating each group of words that you want placed into its own column. To separate delimited text into multiple columns, follow these steps: Highlight the range of text to be separated. Go to Data, Data Tools, Text to Columns. The Convert Text to Columns Wizard opens. In Google Sheets, paste your data into a column. You’ll see a little clipboard icon appear in the lower-right hand corner of your data. Click on that, and you will see the option to split the data into columns. Alternatively, if your data is already in your spreadsheet, head up to the Data menu Split text to columns.

Problem

Typically, in SQL Server data tables and views, values such as a person's name or theiraddress is stored in either a concatenated string or as individual columns for eachpart of the whole value. For example: John Smith 123 Happy St Labor Town, CA. Thisdata could be stored in a single column with all the data, in two columns separatingthe person's name from their address or in multiple columns with a column for eachpiece of the total value of the data.

With this, DBA's are inevitably always having to concatenate or parsethe values to suit our customer's needs.

To build on our sample from above, I have an address column that has the fulladdress (street number and name, city and state) separated by commas in a concatenatedcolumn. I want to break that data into three columns (street, city and state) respectively.

Solution

For this tip, let's create a test database and test table for the parsing example. In this scenario, we have a street name and number, followed by the city,then the state.

The following code creates a table in the new (test) database with only two columns- an id column and an address column.

Populate the table with some generic data. Notice that the address is one stringvalue in one column.

Confirm the table creation and data entry with the following SQL SELECT statement.

Your results should return, as expected, two columns - the id column and theaddress column. Notice the address column is returned as a single string value inimage 1.

Breaking down the data into individual columns. The next step will be to parse outthe three individual parts of the address that are separated by a comma.

Since we didn't call the id column in the SELECT statement above, the resultsonly returned the address string divided into three columns, each column returninga portion of the address for the respective column as shown in image 2.

Here in the real world, DBA's are often faced with more complex tables or viewsand not just a simple two column table as in the above sample. Although the sampleabove is a great primer for dissecting how to parse a string value, thefollowing section demonstrates a more complex situation.

Again, create a sample table in our test database to work with. Here, we createa slightly more complex table with some additional columns.

Insert some generic data into the test table.

Now, let's create a mixed SELECT statement to pull all the columns that breaksdown the 'empName', 'empAddress' and 'empPhone' columns into multiple columns.

In the following block of code, notice that I restarted my 'place / position'count on each column that I want to parse. Each time you start parsing a new column, you must start your count over. You should also note that the 'empName'and 'empAddress' string values are separated with a comma while the 'empPhone' stringvalue is separated by a hyphen and thus should be reflected in your 'parse' functionbetween the first set of single quotes.

Split Text To Columns In For Addresses Free

The results should return thirteen columns from the six columns queried againstas shown in image 3.

In summary, the PARSENAME function is a handy addition to your T-SQL toolkit forwriting queries involving delimited data. It allows for parsing out and returningindividual segments of a string value into separate columns. Since the PARSENAMEfunction breaks down the string, you are not obligated to return all the delimitedvalues. As in our sample above, you could have returned only the area code fromthe 'empPhone' column to filter certain area codes in your search.

Next Steps

Split Text To Columns

About the author

Split Text To Columns In Numbers