如何拆分字符串？ [英] How do I split strings ?

问题描述

我如何拆分字符串。在一个领域我有一个城市州和拉链的数据



例如： -

地址3包含

< br $>
Florida fl 40005

los angeles al 50066



我需要将地址3拆分为佛罗里达州佛罗里达州邮政编码 - 40006



我尝试过使用substring和charindex但是在洛杉矶我的逻辑错了。



有人可以帮忙吗？



提前致谢..



我尝试了什么：



选择SUBSTRING（@ address3,1，charindex（''，@ address3））

Go--

florida

los - 这出错

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

推荐答案

实现这一目标的方法很少，即：

1.通过创建自定义功能：

- 将列中逗号分隔的数据转换为行以供选择 [ ^ ]

- 使用分隔符将字符串拆分为行/列 [ ^ ]

2.通过创建CTE查询：

- 如何使用存储过程在sql server 2008中拆分字符串并将数据插入表 [ ^ ]

- 将字符串拆分为数组并在SQL Server 2014中获取数组 [ ^ ]

- 如何在Sql中使用分隔符拆分字符串 [ ^ ]



祝你好运！

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!

根据样本数据，假设地址格式始终相同。我建议使用 PATHINDEX + expression + RIGHT + REVERSE 操作字符串的函数。见下面的例子。要查找城市名称，请使用从位置0到州名缩写位置的子字符串。



要查找状态位置，请假设状态始终以空格，然后是两个字符，后跟一个空格，然后跟一个数字。等等...

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

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

这篇关于如何拆分字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！