substring & charindex - 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)

substring & charindex

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-02-24 : 09:03:27

I am trying to create a view that splits
an address field down to multiple fields.
The address field may or may not contain up to 5 rows.

I have made a start, but am having trouble where there is only the first line.

select
substring(cuaddress,
1,
charindex(char(13), cuaddress,1)-1) AS ADDRESS1,
substring(cuaddress,
charindex(char(13), cuaddress,1)+1,
len(substring(cuaddress,
1,
charindex(char(13), cuaddress,1)+2))) AS ADDRESS2

from table
where code = 'ANIM03'

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-24 : 09:11:10

Go for a udf like
http://www.nigelrivett.net/SQLTsql/f_GetEntryDelimiitted.html

then you can return nulls from the udf if the entries don't exist

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-02-24 : 09:26:53

I don't wish to appear stupid, but.
Where do I substitute my char(13) in your code and could you explain
the parameters passed to the function

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-24 : 10:54:44

Is char(13) the delimitter? Then
declare @s varcgar(1)
select @s = char(13)
select dbo.f_GetEntryDelimiitted(cuaddress, 1, @s, 'N') ,
dbo.f_GetEntryDelimiitted(cuaddress, 2, @s, 'N') ,
dbo.f_GetEntryDelimiitted(cuaddress, 3, @s, 'N') ,
dbo.f_GetEntryDelimiitted(cuaddress, 4, @s, 'N')
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-02-26 : 15:53:33

I have modified your original code to accomodate the carriage returns, but I get an error when the address field is empty and I am trying to call the function more than once.

The error reads
Invalid length parameter passed to the substring function

I have included the code below. I have a vague idea what is going on, but
am not too sure how to fix it.

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

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
If @line > 0
SELECT @i = @pos + LEN(@delim)

END

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

/**

select
add1 = dbo.F_FormatAddress (cuaddress,1,char(13)),
add2 = dbo.F_FormatAddress (cuaddress,2,char(13)),
add3 = dbo.F_FormatAddress (cuaddress,3,char(13)),
add4 = dbo.F_FormatAddress (cuaddress,4,char(13)),
add5 = dbo.F_FormatAddress (cuaddress,5,char(13))

from table
where code = 'A'

**/

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-28 : 19:06:33

if @pos > @i
SELECT @s = SUBSTRING(@address,@i,@pos - @i)
Return @s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources