Field value Like field value - 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

SQL Server Development (2000)

Field value Like field value

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2006-03-06 : 15:57:07

I have a table with 30+ values with the following codes

pkCode
BUAD
MBT
MS
BT

In my detail table I have concatenated values in a single field like the following:

CODE_DETAILS
UG,BUAD,MST
MS,ENG,TBTD
ADFC,ENG,MBT
MBT,TWNF,MST

How can I write a query that does a like match for any value in the primary key table against the detail table without hardcoding the like condition.

You can do anything at www.zombo.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-06 : 16:00:59

any chance you could store the codes in a normaized fasion and then, if necessary for diplay purposes, create a udf that returns the concatenation? That would be much eaier and faster than storing the concatenated values.

EDIT:
easier and faster for searching anyway.

Be One with the Optimizer
TG

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2006-03-06 : 16:10:38

The detail data comes from a mainframe system for which I have no integrity controls. The concatenated values could be turned into a recordset using a udf, but I was hoping for a quick query solution.

You can do anything at www.zombo.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-06 : 16:29:29

depends on your desired output but a quick, dirty, and very SLOW query could be this:


create table #lookup (pkCode varchar(10))
create table #detail (Codes varchar(50))

insert #lookup
select 'BUAD' union 
select 'MBT' union 
select 'MS' union 
select 'BT'

insert #detail
select 'UG,BUAD,MST' union
select 'MS,ENG,TBTD' union
select 'ADFC,ENG,MBT' union
select 'MBT,TWNF,MST'


select	l.pkCode
	,d.codes
from	#lookup l
join	#detail d
		on ',' + d.codes + ',' like replace('%,' + l.pkcode + ',%',' ', '')



drop table #lookup
drop table #detail


output:
pkCode     codes                                              
---------- -------------------------------------------------- 
BUAD       UG,BUAD,MST
MBT        ADFC,ENG,MBT
MBT        MBT,TWNF,MST
MS         MS,ENG,TBTD

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources