delimited table fields sql server 2005

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)

delimited table fields sql server 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-07-04 : 09:57:24

Hi,

I have a table with field BillToReference that can have the following values:
6490198342,6490198389
6490198316
6490198389
6490151828,6498029383,6498034639,6499013103,6499015567

I need to write a query that returns all the values in billtoreference.
Select * from invoice where invoice_id in (select BillToReference from Export)
I don't know how to do it when some values are delimited and others not.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-04 : 10:28:04

declare @Export table (BillToReference varchar(1000))
declare @Invoice table(invoice_id varchar(25), invoice_text varchar(255))

insert @Export
select '6490198342,6490198389' union all
select '6490198316' union all
select '6490198389' union all
select '6490151828,6498029383,6498034639,6499013103,6499015567'

--select * from @Export

insert @Invoice
select '6498029383', 'Test-Invoice 1' union all
select '6490198389', 'Test-Invoice 2' union all
select '6499015567', 'Test-Invoice 3'

--select * from @Invoice

select distinct i.*
from @Invoice i
join @Export e
on e.BillToReference like '%'+i.invoice_id+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources