CODEWITHSUNDEEP
sql-server
SQLiz
SQLiz

Reputation: 143

SQL Server - Split a field into 4 columns

I have a function that split an address line into 4 columns when it encounters a carriage return. It is a multi statement table valued function. How can I use it to split the address line field from the address table? I cannot use joins because it uses the table fields as paramaters. Any clue?

SELECT [ID], [ADDRESS_LINE] FROM [dbo].[ADDRESS]

The function needs 3 arguments: Address_ID, Address string and separator which is carriage return.

[dbo].[fn_Split_Address](ID, ADDRESS_LINE, CHAR(13))

Example to help understand:

SELECT A.Addresse_ID, A.Adresse_1, A.Adresse_2, A.Adresse_3,
A.Adresse_4 FROM RE7_Pass.[dbo].[fn_Split_Address](888999, '10
Burlington street' + CHAR(13) + 'Chicago'+ CHAR(13) +
'Illinois',CHAR(13)) AS A

The result should be as below:

enter image description here

Upvotes: 2

Views: 76

Answers (1)

Thom A
Thom A

Reputation: 95588

Expanding on Pawan's comment:

SELECT A.[ID],
       A.[ADDRESS_LINE],
       SA.Address1,
       SA.Address2,
       SA.Address3,
       SA.Address4
FROM [dbo].[ADDRESS] A
     CROSS APPLY [dbo].[fn_Split_Address] (A.ID, A.Address_Line, CHAR(13)) SA;

The documentation on the FROM clause explains well on how to get data from a TVF: Using APPLY

Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.

It also gives further examples further down the page, at using CROSS APPLY. The example they provide being:

USE master;  
GO  
SELECT dbid, object_id, query_plan   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);   
GO

Upvotes: 4

Related Questions