街道地址使用sql server清理 [英] Street Address Clean up using sql server

问题描述

您好，


任何人都可以提供帮助。我有一个街道地址文件，我想将文件中的信息拆分成不同的列。


例如100 SW 14th Street N，Suite 120


首先。该脚本将100捕获到名为StreetNo的新列中。正在考虑脚本进行计数直到找到空格然后停止的情况。


其次。该脚本将提取SW。进入另一个名为DirectionPrefix的列。因此，如果它在streetNo之后立即找到任何N，E，W，S，NE，NW，SE，SW，它应该捕获该值并将其存储在DirectionPrefix列中。


第三。在N，E，W，S，NE，NW，SE，SW之后立即找到的所有值（如果有的话）应存储在新列（StreetName）中。脚本会计数，直到找到街道类型（道路，大道，大道）。但是这个值将包括街道类型。对于上面的例子 - 街道。


第四。该脚本将捕获该道路的街道类型。至于上面的例子 - 街。


第五。搜索街道类型后是否存在N，E，W，S，NE，NW，SE，SW。如果是的话，捕获并将其放入另一列。


第六。第五步之后的所有内容都应该被捕获并放入第六步。


谢谢。

Hello ,

Can any one assist. I have a Street Address file and i want to split the information in the file into different columns.

For example "100 SW 14th Street N, Suite 120"

First. The script will capture the 100 into a new column called StreetNo. Was thinking of a scenario where the script does a count until it finds a space then stops.

Second. The script will extract "SW" into another column called DirectionPrefix. So if it find any N, E, W, S, NE, NW, SE, SW immediately after the streetNo, it should capture that value and store it the the DirectionPrefix column.

Thirdly. All the value found immediately after the N, E, W, S, NE, NW, SE, SW, if there are any, should be store in a new column (StreetName). The scripts does the count until it find a Street Type (Road, Avenue, Blvd). but this value will be include a street type.As for the example above - Street.

Fourth. The script will capture the street type for that road. as for the example above - Street.

Fifth. Search if any N, E, W, S, NE, NW, SE, SW exist immediately after the street type. if yes, capture and place that into another column.

Sixth. Everything after the Fifth step should be capture and placed into the sixth columm.

Thank you.

推荐答案

使用具有开始和结束参数的子字符串应该满足您的需要，结束参数是当前字符串部分的长度，例如100将具有长度3，依此类推。


记住开始位置0。

Use of a substring with start and end parameters should cater for what you need, with end parameter being the length of the current string part, e.g 100 would have length 3 and so on.

Remember to start at position 0.

谢谢。但问题是地址编号从一个数字值到六个数值不等。

Thanks. But the issue is the address number vary from one nemeric value to six numeric values.

如何使用带空格字符的SQL拆分函数作为分隔符？

How about using a SQL split function with a space character as the delimeter?

这篇关于街道地址使用sql server清理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！