How do I split strings ?

Question

1.00/5 (2 votes)

See more:

How do i split strings. In one field i have a data of city state and zip

e.g:-
Address3 contains

Florida fl 40005
los angeles al 50066

I need to split address3 into City florida state fl and zip - 40006

I have tried using substring and charindex but in los angeles my logic gets wrong.

Can anyone help ?

Thanks in advance..

What I have tried:

select SUBSTRING(@address3,1,charindex(' ',@address3))
Go--
florida
los -- this gets wrong

Posted 5-Sep-18 15:08pm

Member 13138564

Updated 5-Sep-18 21:31pm

Add a Solution

Comments

RedDk 5-Sep-18 21:22pm

Both of those returns are correct. The best reference to use in any case is the Book-on-line help that ships with SSMSE. You don't even have to look anything up. Put your cursor on the KEYWORD "SUBSTRING" hit F1 and you've got half a dozen examples showing what is proper. Down under the F1 object then there's a host of related terms and methods and functions. This is the best course of action. Frankly, this question is silly.

2 solutions

Solution 2

There's few ways to achieve that, ie.:
1. by creating custom function:
- Converting comma separated data in a column to rows for selection[^]
- Split string into Rows / Columns using Delimiters[^]
2. by creating CTE query:
- How to split a string in sql server 2008 using stored procedure and insert the data to table[^]
- Split string into array and get array in SQL server 2014[^]
- How a split a String using delimiter in Sql[^]

Good luck!

Posted 5-Sep-18 21:31pm

Maciej Los

Updated 5-Sep-18 21:32pm

v2

Comments

CHill60 6-Sep-18 3:57am

Don't forget from SQL 2016 onwards there is also STRING_SPLIT (Transact-SQL) | Microsoft Docs[^] although they'd still have to be clever with realising they still need to start at the end to handle spaces in the city name :-)

Maciej Los 6-Sep-18 4:03am

Goog point, Caroline! Thanks :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2018-09-05T18:34:00

Based on the sample data, the assumption is the address format always the same. I would suggest to use PATHINDEX + expression + RIGHT + REVERSE functions to manipulate the string. See below example. To find the city name, use the substring from position 0 to position of the state abbreviation.

To find the state position, assume that the state will always start with a space, then two characters, follow by a space and then follow by a number. etc...

SQL

DECLARE @table1 TABLE (
	Address1	VARCHAR(50)
)

INSERT INTO @table1
	SELECT 'Florida fl 40005' UNION
	SELECT 'los angeles al 50066' UNION
	SELECT ' EL Paso TX 79901'

SELECT  
City = SUBSTRING(Address1, 0, PATINDEX('%[ ][a-z][a-z][ ][1-9]%',Address1)),
[State] = SUBSTRING(Address1, PATINDEX('%[ ][a-z][a-z][ ][1-9]%',Address1), 3),
Zip = RIGHT(Address1, CHARINDEX(' ', REVERSE(Address1)) - 1) FROM @table1

Output:

City	        State	Zip
 EL Paso	     TX	    79901
Florida	         fl	    40005
los angeles	     al	    50066