address split function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

address split function

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-29 : 05:26:41

I am trying to split an address into 4 lines and have used the follwing function, but am having a problem when the end of the address field does not have a carriage return. See below

CREATE Function FormatSupplierAddress
(
@address VARCHAR(160),
@line INT
)
Returns VARCHAR(100)
as
BEGIN
Declare @i INT, @s VARCHAR(100), @pos INT, @delim VARCHAR(13)

SELECT @delim = char(13)
SELECT @i = 1
WHILE @line > 0
BEGIN
SELECT @line = @line - 1
SELECT @pos = CHARINDEX(@delim,@address,@i)
If @pos = 0
SELECT @pos = DATALENGTH(@address) + 1
ELSE
BEGIN
If @line > 0
SELECT @i = @pos + LEN(@delim)
END
END

SELECT @s = SUBSTRING(@address,@i,@pos - @i)
RETURN @s
END

If there is no char(13) at the end of the address in line 1, it works fine, but if the function is used for line 2 or above, the function always returns the line 1 split value.

I know what the problem is , but am not sure how to fix it.

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 06:45:37

Add a dummy carriage return to the end of the address (@address) before you split it?

Kristen

wotrac
Yak Posting Veteran

98 Posts

Posted - 2006-01-29 : 07:10:21

Will I need to check for the existence of one before adding the carriage return, or won't it matter

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-29 : 07:16:32

Yes. You need to check for existence of a char(13) before adding it. Try it and you will see

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 04:34:30

Also refer SeventhNight's function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

and do query

declare @Address varchar(100),@delim varchar(10),@line int
set @Address='test1'+char(13)+'test2'
set @delim=char(13)
set @line=2
Select data from dbo.split(@Address,@delim) where id=@line

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources