Comparing comma delimited 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 2000 Forums

SQL Server Development (2000)

Comparing comma delimited columns

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2008-10-10 : 12:13:40

I have a column that holds a doctors degree(MD, DO, MSW) and I need to compare it against a master list which may not have the degree in the same order(DO, MD, MSW).

I've been looking at several split functions, but I can't seem to figure out how to apply this to a column in both tables and then use the end result. They doctor may also have a partial degree(MD, DO). I need to return results of any in error(DO, MB).

Here is some sample data.
Table1
doc specialty degree
1 General MD, DO
2 Therapy PT, MS
3 General MB, DO

Table2
Specialty degree
General DO, MD
Therapy PT, MS, MSW

Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 17:58:29

Hi, I have made a function to sort a comma delimited string.
now you can compare one table to another

Enjoy
[code]
CREATE function SortString(
@string varchar(8000) )
returns varchar(8000)
as
BEGIN

set @string = replace(@string,' ','')

declare @pos int
declare @piece varchar(500)

if right(rtrim(@string),1) <> ','
set @string = @string + ','

set @pos = patindex('%,%' , @string)

declare @sort_table table (id int identity(1,1) not null, sortstr varchar(500))

while @pos <> 0
begin
set @piece = left(@string, @pos - 1)

insert into @sort_table (sortstr) values(cast(@piece as varchar(500)))

set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%,%' , @string)
end

declare @catstr varchar(8000), @thisstr varchar(500)
set @catstr = ''
declare curs cursor for
select sortstr from @sort_table order by sortstr

open curs

fetch next from curs into @thisstr

while @@FETCH_STATUS=0
begin

set @catstr = @catstr + @thisstr
fetch next from curs into @thisstr
if @@FETCH_STATUS=0
begin
set @catstr = @catstr + ','
end

end
close curs
deallocate curs

return @catstr
END
[/CODE]

memarcie
Yak Posting Veteran

91 Posts

Posted - 2008-10-15 : 07:33:57

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources