Creating a function

[paxmaster]

trying to extract fulladdress to individual column such as address1 address2 City State Zip, and each of the fulladdress has a comma

so that why I am trying to create a function that count the comma and put the fulladdress ito individual column.

hear is the script that I am making but i got stuck at city

alter function Address(@fulladdress varchar(100),@section varchar(35))


returns varchar(100)
as

begin

declare @desiredadd varchar (100)
declare @lenght int
declare @i int
declare @comma1 int
declare @comma2 int
declare @comma3 int
declare @comma4 int

--set @desiredadd = @fulladdress
set @i = 1
set @lenght = len(@fulladdress)


--while (@i <= @length)
--begin
if @section ='add1' begin
set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,1,@comma1-1)
end

else if @section ='add2' begin

set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,@comma1+1,len(@fulladdress))
set @comma2 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@desiredadd,1,@comma2-1)
end

else if @section ='city' begin

set @comma1 = charindex (',',@fulladdress,1)
set @desiredadd=substring(@fulladdress,@comma1+1,len(@fulladdress))
set @comma2 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@fulladdress,1,@comma2-1)
set @comma3 = charindex (',',@desiredadd,1)
set @desiredadd=substring(@desiredadd,1,@comma3-1)
end

return @desiredadd
end

[Ramy Mahrous]

Tell me sample data and illustrate what you need to get from them

[paxmaster]

for example I have 2 table one table is DeveloperTeam1 that has only 2 column call Fullname and FullAddress

INSERT INTO DeveloperTeam1 VALUES (
'Nora Sing', '123 Street, Apt No 75, Jersey City, NJ, 07506'

and other table is 
create table  DeveloperTeamExpanded (

FirstName varchar(50), 
LastName varchar(50), 
AddressLine1 varchar(50), 
AddressLine2 varchar(50), 
City varchar(50), 
State varchar(50), 
Zip varchar(50)
)

so I want extract the address part in AddressLine1 AddressLine2 City State and Zip by using that function That I try to created as you see there is always a comma <-- the on I am going use a store proc to enter this in DeveloperTeamExpanded

123 Street in above `example should go to addressline1
Apt No 75 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' addressline2
Jersey City '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' City
NJ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''State
07506''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''zipcode

But i am stuck at City

thanks for replying my post

[Ramy Mahrous]

I searched I found this Function

CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

It returns table each row represents the string after the split
So select * from dbo.Split(N'ramy,abdalla,mahrous', N',') returns
ID Data
1 ramy
2 abdallah
3 mahrous

I got it from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

[paxmaster]

thanks for the help the script it works
But some part of the script I don't understand can you please clarify
like this part

While (Charindex(@SplitOn,@RowData)>0)
Begin
INSERT INTO @RtnValue (DATA)
SELECT
DATA = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

SET @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
SET @Cnt = @Cnt + 1
End

INSERT INTO @RtnValue (DATA)
SELECT DATA = ltrim(rtrim(@RowData))

[Ramy Mahrous]

hmmmm. What I need from you is to read in ltrim, rtrim, charindex functions in T-SQL, and have a look on the script again.

[paxmaster]

ok now I understand what it mean's
now How can i call a table with this function
from your previous post you did this select * from dbo.Split(N'ramy,abdalla,mahrous', N',')

it bring out this ramy abdalla mahrous

but i want to pull all out from a table, not one by one

Thanks for you big help, I am learning something everyday!

[Ramy Mahrous]

What version you use?

[paxmaster]

sql 2000

[Ramy Mahrous]

OK, let me some time to do it... by today or tomorrow max I'll handle this en shaa ALLAH.

[paxmaster]

Thanks

AllahHafiz

[Ramy Mahrous]

I didn't do any progress because of a lot of load, please try to search and tell me your progress, keep in touch.