Compare Items in Table Columns

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 2005 Forums

Transact-SQL (2005)

Compare Items in Table Columns

Author

Topic

rksprst
Starting Member

3 Posts

Posted - 2007-12-20 : 01:22:19

I have a column in my database that contains words separated by commas. I want to write a stored procedure that separates this column by the commas into something like an array of strings.

I than want to do two things, return that array and compare the elements of that array to a similar array (counting the number of elements that are the same).

I can program this in C#, but I want to do it on the sql side... however, I'm not sure what the sql code would look like (I've only written selects, inserts, updates, etc...)

Can anyone give me any tips or direct me to some links that would explain how to do something like this. I've googled and haven't found anything useful. Thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-20 : 01:28:39

Check out the post over here...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Chirag

http://www.chirikworld.com

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-20 : 01:30:58

I have a function that I stole from an article here written by Graz and changed it a bit to suite my needs.
Something Like this......

CREATE Function [dbo].[CsvToVarChar50] ( @Array varchar(max))
returns @VC50Table table
(VC50Value VARCHAR(50))
AS
begin
if @Array IS NULL
begin
INSERT @VC50Table VALUES(NULL)
return
end

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

declare @separator_position int
declare @array_value varchar(max)

set @array = @array + ','

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

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

Insert @VC50Table
Values (Cast(@array_value as VARCHAR(50)))

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

return
end

Usage would by like this:

select vc50value from dbo.CsvToVarChar50 ('123,abc,456,def')

results:
123
abc
456
def

Is that what you are looking for?

Duane.

rksprst
Starting Member

3 Posts

Posted - 2007-12-20 : 01:33:02

Thanks. Looks like a bunch of good tutorials. Will take a look.

rksprst
Starting Member

3 Posts

Posted - 2007-12-20 : 01:37:43

Duane, thanks... that definitely takes me in the right direction... very similar to what I want to do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 02:26:05

or even this function;-

CREATE FUNCTION SplitStringValues
@StringValues varchar(max)
RETURNS @Results TABLE
(Value varchar(50))
AS
DECLARE @N varchar(50),@Pos int

WHILE @StringValues IS NOT NULL
BEGIN
SET @Pos=CHARINDEX(',',@StringValues)
CASE
WHEN @Pos >0 THEN
SET @N=LEFT (@StringValues,@Pos-1)
ELSE @N=@StringValues
END
INSERT INTO @Results VALUES (@N)
SET @StringValues= CASE WHEN @Pos >0 THEN RIGHT(@StringValues,@Pos+1) ELSE NULL END
END
GO

the table @Results contain array of strings

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 03:00:50

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources