0

My data source looks like this

John Doe| 26 CHANCE ROAD SUTTON SURREY |

In the second column each address line is separated with a char(10) +char(13)

I want my output to look like this

26 CHANCE ROAD | SUTTON | SURREY|

I have been using substring but I've only been able to bring out the first address line using

SUBSTRING(dbo.Address, 0, CHARINDEX(CHAR(13) + CHAR(10), dbo.Address + CHAR(13) + CHAR(10))) as ADDRESS_1,

I am using MSSQL

greej001
  • 1
  • 1

3 Answers3

0

If you on SQL version >=2016 and your database compatibility level set at least to 130 you can use STRING_SPLIT ( string , separator ) function More info here: https://www.mssqltips.com/sqlservertip/4884/sql-server-2016-stringsplit-function/

for another SQL server/compatibility versions, you need to write your own function and I bet you can find lots of similar on StackOverflow also. like here: How to split a comma-separated value to columns or here T-SQL split string or here... How do I split a string so I can access item x?

and list can continue.

Seichelis
  • 35
  • 4
  • The separator for STRING_SPLIT can only be a single character. In this case, trying to split on CHAR(13)+CHAR(10) will generate an error: Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'. – Stephen Wuebker Dec 03 '19 at 16:37
  • Thanks, currently stuck on SQL server 2006 until next year – greej001 Dec 04 '19 at 10:27
  • if you have more then 1 split character, you can always use Replace function upfront to replace your separators to some unused in-text character like instead CHAR(13)+CHAR(10) replace it only to Char(10) or to some pipes or some Û – Seichelis Dec 04 '19 at 14:23
0

STRING_SPLIT is a good option if you are using SQL Server 2016 or higher and your database is set to a compatibility level of at least 130. However, the separator for STRING_SPLIT can only be a single character. In your case, you have two characters as the separator. In your sample, you've pretty much got the first part of the address. You can get the rest with the following:

SUBSTRING(Address, 1, CHARINDEX(CHAR(13) + CHAR(10), Address)) as ADDRESS_1,
SUBSTRING(Address, CHARINDEX(CHAR(13) + CHAR(10), Address), CHARINDEX(CHAR(13) + CHAR(10), Address,CHARINDEX(CHAR(13) + CHAR(10), Address)+1)-CHARINDEX(CHAR(13) + CHAR(10), Address)) as ADDRESS_2,
REVERSE(SUBSTRING(REVERSE(Address), 1, CHARINDEX(CHAR(10) + CHAR(13), REVERSE(Address)))) as ADDRESS_3

SUBSTRING() takes 3 arguments: expression, start, length. The numbering is 1 based, not 0. CHARINDEX takes 3 arguments: expressionToFind, expressionToSearch, and optionally start_location

So to get the second part of the address, you can just start the substring at the first charindex. The length is the charindex of the next separator - the charindex of the first.

The third part is easier: Just reverse the string and the order of your separator characters to get the substring, and then just reverse that substring.

Stephen Wuebker
  • 189
  • 6
  • 2
    You can just replace the double character delimiter to use STRING_SPLIT: `STRING_SPLIT (REPLACE(Address, CHAR(13)+CHAR(10), CHAR(13)), CHAR(13))` – bendataclear Dec 03 '19 at 16:59
  • thanks, I seem to get the error this error when i run the query, annoyingly i'm stuck on SQL server 2006 until next year. Msg 537, Level 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. Completion time: 2019-12-04T10:25:12.4339820+00:00 – greej001 Dec 04 '19 at 10:26
0

Without using STRING_SPLIT and sticking only to SUBSTRING and CHARINDEX (lots of them), and assuming that your address data is normalized enough (e.g. all values for [address] will have four instances of ' ' and there are not going to be cases with Apartment Number or Streets with spaces in the name), the following code should split those addresses into three columns: Street, City, and County.

create table #temp (client nvarchar(30),address nvarchar(50))
insert into #temp values ('John Doe', '26 CHANCE ROAD SUTTON SURREY')

select SUBSTRING(address,1,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address)+1)+1)) as [Street] 
        --starts at beginning, length goes to 3rd instance of ' '
    ,SUBSTRING(address,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address)+1)+1)
        ,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address)+1)+1)+1)-CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address)+1)+1)) as [City] 
        --starts at 3rd instance of ' ', length is distance from 3rd instance of ' ' to 4th instance
    ,SUBSTRING(address,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address,CHARINDEX(' ',address)+1)+1)+1),20) as [County] 
        --starts at 4th instance of ' ', length just needs to go through to the end
from #temp
Andrew Edwards
  • 65
  • 5