Multiple values? - 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)

Multiple values?

Author

Topic

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-24 : 10:23:23

Help!!!


create table table1 (column1 varchar(50))
create table table2 (column1 int, column2 varchar(50))

insert into table1 values ('1,2,3,4,5')
insert into table2
select 1,'a'
union select 2,'b'
union select 3,'c'
union select 4,'d'
union select 5,'e'

If I have say 1,2,3,4,5 in table1 column1 (varchar) and I want to match this to each value in table2 column1 (int), how would I go about it?

Having major headaches on this one as keep getting a conversion error, though I can't convert to an int as the comma's will make it error. Does anyone know a way around this?

Cheers

Rick

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-24 : 10:42:04

U can use a split function (u can find by searching this forum) and get each values seperated
Or else u can write ur own function using charindex, substring etc (check BOL for string functions)
Also u may need to use convert() to convert between int & varchar

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-24 : 10:57:05

Give us a sample result set of what you expect

If you want your computer to be faster then throw it out of the window.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-24 : 11:06:09

[ESPServer=on]


create table #table1 (column1 varchar(50))
create table #table2 (column1 int, column2 varchar(50))

insert into #table1 values ('1,2,4,5')
insert into #table2
select 1,'a'
union select 2,'b'
union select 3,'c'
union select 4,'d'
union select 5,'e'

Select * 
From #table2 A
Inner Join #table1 B
On ','+B.column1+',' like '%,'+convert(varchar,A.column1)+',%'

Drop Table #table1
Drop Table #table2

[ESPServer=off]

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-24 : 11:27:34

Corey, you're a star.. That is exactly what I was after..

Thank you..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-24 : 11:47:29

so the ESPServer is working

glad it was helpful!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-24 : 12:02:08

It must be..

Got stuck on that one, was hard enough getting the values in the original format out of a list.. (read crap designed, third party tools)..

Thanks again..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources