Querying Data String - 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

General SQL Server Forums

New to SQL Server Programming

Querying Data String

Author

Topic

damiansadler
Starting Member

6 Posts

Posted - 2008-12-17 : 11:12:12

Hello,

Hope someone can help with a basic question.

I'm trying to query a field that has a strings in such as Microsoft Office Professional 2003 with a field in another table which holds Office

Normally the script I would use would be;

select * from Software where Applcation like '%Office%'

However I'm looking to replace '%office%' with the field name from the other table.

So it would be something like;

select * from Software where Applcation like table2.App_name

Clear as mud?

Any help appreciated
D.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 11:14:07

select * from Table1 AS t1
inner join Software AS sw on sw.Applcation like '%' + t1.App_name + '%'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:15:11

[code]
select s.* from Software s
inner join table2 t2
on s.Applcation like '%'+ t2.App_name + '%'
[/code]

damiansadler
Starting Member

6 Posts

Posted - 2008-12-17 : 11:32:16

Sorry i'm still not getting anything back.

This is what i've translated your code to;

select * from software_details
inner join audit on software_details.publisher like '%'+audit.software+'%'

software_details.publisher for example would contain Microsoft
audit.software would contain Microsoft office 2003 professional

I know I'm doing something wrong
D.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:38:14

then it should be other way around

select * from software_details
inner join audit on audit.software like '%'+software_details.publisher+'%'

damiansadler
Starting Member

6 Posts

Posted - 2008-12-17 : 11:48:22

Hmm still returns no results

I've done a bit more and get the exact results I need using this;

select distinct pc_no from audit
inner join software_details on audit.software like '%Microsoft%'

but I'd like to replace the Microsoft with software_details.publisher

However when i do this there are no results?

Am I approaching this the wrong way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:51:22

quote:
Originally posted by damiansadler

Hmm still returns no results

I've done a bit more and get the exact results I need using this;

select distinct pc_no from audit
inner join software_details on audit.software like '%Microsoft%'

but I'd like to replace the Microsoft with software_details.publisher

However when i do this there are no results?

I'm I approaching this the wrong way?

i dont think this is right way as above query just gives you cross join of records containing Microsoft in software field of audit with all records of software_details table which i dont think is what you're looking at

damiansadler
Starting Member

6 Posts

Posted - 2008-12-17 : 11:54:30

So I guess what I'm looking for is for every unique pc_no list software that matches entries in the publisher field or the other way round.

I think I've confused myself!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 11:58:22

as per your explanation on 12/17/2008 : 11:32:16 what you want is this


select * from software_details
inner join audit on audit.software like '%'+software_details.publisher+'%'

since you say it doesnt work, i've two questions

1. can you post some sample data from two tables (may be 5 rows)
2. Are you using case sensitive collation?

damiansadler
Starting Member

6 Posts

Posted - 2008-12-18 : 05:11:11

Thanks,

Data looks like;

audit table
pc_no software
1 Microsoft Office Professional 2003
1 Adobe Acrobat 7
1 Trend Officescan
2 Microsoft Office Professional Plus 2007
2 Adobe Acrobat 8

Doesn't look like I'm using case sensitive collation its a standard SQL 2000 install.

Thanks,
D.

damiansadler
Starting Member

6 Posts

Posted - 2008-12-18 : 05:15:18

Sorry the software_details table would have the following;

ID(unique identifier) Publisher Application Version
Adobe Acrobat 7
Microsoft Office 2003

Thanks again,
D.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources