How to use this covert CSVToVarchar function

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

SQL Server Development (2000)

How to use this covert CSVToVarchar function

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-07-06 : 13:59:58

Dear all ,
I have two table. Both part have interest field. All interest field are entered in csv format. I would like to use this function in normal select query

select * from CSVTOVarChar((@query))
or even
select * from CSVTOVarChar((select Info2 from Testtable5 where Id = 29))

//Error Message
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.

Any suggestion?

Thx!!

/******************/
dbo.CsvToVarchar ( @Array varchar(1000))
returns @StringTable table
(StringValue varchar(50))
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
select @array_value = RTrim(@array_value)
select @array_value = LTrim(@array_value)

Insert @StringTable
Values (Cast(@array_value as varchar(50)))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 14:06:53

Here's 2 ways to use it:


declare @csv varchar(50)
set @csv = '29,30,31'

select * from dbo.CsvToVarchar(@csv)

select a.myCol
from	myTable a
join	dbo.CsvToVarchar(@csv) b 
		on a.myIDCol = b.StringValue

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources