cross apply

Question

Post reply

cross apply

kevin4u06

SSCrazy

Points: 2093
More actions
November 23, 2010 at 3:39 pm

#229043

Hi,
i have a table with these columns,
------------------
id address name
------------------
and I have a function which parses the address field into city state and zip. we have to pass the address column as a variable to this function. This function returns a table.
Now in sql 2005 we can use cross aplly to do this as below
select f.* from Table as t
cross apply function(t.address, ',') f
Is there a way to this on sql 2000, i.e, to pass a column into this function
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

WayneS SSC Guru Points: 95409 More actions · Answer 1

SELECT column = (select column FROM function (t.address))

FROM Table t

you would have to repeat the call for each column you want.

On 2000, you might want to include an address_id (or similar) in the output, then you can do:

SELECT f.*

FROM Table t

JOIN function f

ON f.address_id = t.address_id

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kevin4u06 SSCrazy Points: 2093 More actions · Answer 2

I dont want to join the function and the table ...i want to pass data in the column from the table as a variable to the function

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

Maybe if you show us the function, it will be possible to replace it with something that will work in 2000?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

kevin4u06 (11/23/2010)
I dont want to join the function and the table ...i want to pass data in the column from the table as a variable to the function

Cross Apply is nothing more than a correlated subquery... an inner join. Try the suggestion. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevin4u06 SSCrazy Points: 2093 More actions · Answer 5

This is my function

CREATE FUNCTION [dbo].[ParseAddress]

(

@Address VARCHAR(8000) ,

@Delimiter VARCHAR(10)

)

RETURNS @Return TABLE ( AddressLine1 VARCHAR(100),

AddressLine2 VARCHAR(100),

City VARCHAR(50),

StateName VARCHAR(50),

ZipCode CHAR(10)

)

AS

BEGIN

DECLARE @StateAndZipCode VARCHAR(500)

DECLARE @City VARCHAR(100)

DECLARE @AddressLine2 VARCHAR(200)

DECLARE @AddressLine1 VARCHAR(200)

--Parse State & Zip Code

IF CHARINDEX(@Delimiter, @Address ) > 1

BEGIN

SELECT @StateAndZipCode = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(@Address ), CHARINDEX(@Delimiter, REVERSE(@Address )) -1))))

SELECT @Address = REPLACE(@Address , + @Delimiter + ' ' + @StateAndZipCode,'')

END

--Parse City

IF CHARINDEX(@Delimiter, @Address ) > 1

BEGIN

SELECT @City = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(@Address ), CHARINDEX(@Delimiter, REVERSE(@Address )) -1))))

SELECT @Address = REPLACE(@Address , + @Delimiter + ' ' + @City,'')

END

--Parse Address Line 2

IF CHARINDEX(@Delimiter, @Address ) > 1

BEGIN

SELECT @AddressLine2 = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(@Address ), CHARINDEX(@Delimiter, REVERSE(@Address )) -1))))

SELECT @Address = REPLACE(@Address , + @Delimiter + ' ' + @AddressLine2,'')

END

--Parse Address Line 1

IF LEN(LTRIM(RTRIM(@Address ))) > 0

BEGIN

SELECT @AddressLine1 = LTRIM(RTRIM(@Address ))

END

Else

BEGIN

SELECT @AddressLine1 = @AddressLine2

END

IF @StateAndZipCode IS NULL

BEGIN

INSERT INTO @Return

SELECT

NULL,

REPLACE(@AddressLine1, RIGHT(@AddressLine1,CHARINDEX(' ', REVERSE(@AddressLine1))),'') AS StateName,

CASE LEN(LTRIM(RTRIM(RIGHT(@AddressLine1,CHARINDEX(' ', REVERSE(@AddressLine1))))))

WHEN 5 THEN LEFT(LTRIM(RTRIM(RIGHT(@AddressLine1,CHARINDEX(' ', REVERSE(@AddressLine1))))),5)

WHEN 10 THEN LEFT(LTRIM(RTRIM(RIGHT(@AddressLine1,CHARINDEX(' ', REVERSE(@AddressLine1))))),10)

END AS ZipCode

END

ELSE

IF @City IS NULL

BEGIN

INSERT INTO @Return

SELECT

NULL,

@AddressLine1,

REPLACE(@StateAndZipCode, RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))),'') AS StateName,

CASE LEN(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))))

WHEN 5 THEN LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),5)

WHEN 10 THEN LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),10)

END AS ZipCode

END

ELSE

BEGIN

INSERT INTO @Return

SELECT

@AddressLine1,

@AddressLine2,

@City,

REPLACE(@StateAndZipCode, RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))),'') AS StateName,

CASE LEN(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))))

WHEN 5 THEN LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),5)

WHEN 10 THEN LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),10)

END AS ZipCode

END

RETURN

END

kevin4u06 SSCrazy Points: 2093 More actions · Answer 6

This is the table and sample data (Attached below)

my out put shoud be something like this.

My function does the splitting of the address field .

Thank you in advance

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

kevin4u06 (11/24/2010)
This is the table and sample data (Attached below)
my out put shoud be something like this.
My function does the splitting of the address field .
Thank you in advance

A .JPG file doesn't help us help you. Please see the article at the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

kevin4u06 (11/24/2010)
This is the table and sample data (Attached below)
my out put shoud be something like this.
My function does the splitting of the address field .
Thank you in advance

You might benefit from using a sproc for the whole lot, something like this:

SET NOCOUNT ON

DROP TABLE #AddressLists

DROP TABLE #aNewTempTable

DROP TABLE #Tally

CREATE TABLE #AddressLists (id INT, [address] VARCHAR(100), [name] VARCHAR(50))

INSERT INTO #AddressLists (id, [address], [name])

SELECT 1, 'Appt#26, 101 Greenwood Drive, Houston, Texas, 99999', 'Jones' UNION ALL

SELECT 2, '20 Parkview,,Highgate,London,,', 'Smith'

-- create a tally heap (persist and put a unique clustered index on n for production code)

CREATE TABLE #Tally (n int IDENTITY(1,1))

GO

INSERT INTO #Tally DEFAULT VALUES

GO 100

-- Split the string to new rows

SELECT IDENTITY(int,1,1) AS Sequence,

a.id, a.[name], n,

LTRIM(SUBSTRING(a.[address], n, CHARINDEX(',', a.[address] + ',', n) - n)) AS AddressElement,

AddressElementNo = CAST(NULL AS INT)

INTO #aNewTempTable

FROM #AddressLists a, #Tally nums

WHERE n < LEN(a.[address]) + 2

AND SUBSTRING(',' + a.[address], n, 1) = ','

ORDER BY a.id, N

-- reassemble as columns

SELECT

ID,

[Name],

Address1 = MAX(CASE WHEN RankSequence = 1 THEN AddressElement ELSE NULL END),

Address2 = MAX(CASE WHEN RankSequence = 2 THEN AddressElement ELSE NULL END),

Address3 = MAX(CASE WHEN RankSequence = 3 THEN AddressElement ELSE NULL END),

Address4 = MAX(CASE WHEN RankSequence = 4 THEN AddressElement ELSE NULL END),

Address5 = MAX(CASE WHEN RankSequence = 5 THEN AddressElement ELSE NULL END),

Address6 = MAX(CASE WHEN RankSequence = 6 THEN AddressElement ELSE NULL END)

FROM (

SELECT

RankSequence = t.Sequence - d.MINSequence,

t.ID, t.Name,

t.AddressElement

FROM #aNewTempTable t

INNER JOIN (SELECT Id, MINSequence = MIN(Sequence)-1 FROM #aNewTempTable GROUP BY Id) d ON d.id = t.id

) q

GROUP BY ID, [Name]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden