reversed query - 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)

reversed query

Author

Topic

gw
Starting Member

4 Posts

Posted - 2006-02-18 : 16:57:45

Iam a bit stucked, in my attempt to make a sql query simpler.

The problem is simple enough, in a deployment solution the PC contact diffrent servers based on what subnet they are from, and they copy a drivers folder based og what kind of computer model it is. My sql script write in a DB what server and folder name the PC are going to use.

There are two table that contains the data, one table is subnet, and the other is PC_model

The PC have its own row in a computer table.

In the table PC_model there are the column prod_name and folder_name.

The core of the problem is that I start out with data from the computer that is a long text string, and have to find a match in a shorter text string.

The long text string can be "HP Compaq nc6220 (PG832EA#ABN)" since this text string vary slighty for the same model series, I need to find a match on a short text string like in this case "nc6220"

My query today do a nested IF check for every possible PC model. What I want is just to make a query against the folder_name table, and not have to edit the sql query for every new PC model.

Its simple to make a query when starting with a short text string and use the like '%nc6220%', but what do I do when starting with the long text string?

I will try to not use the freetext command, becase of no index support on the MSDE.

Iam starting to learn about UDF and trying to figure out how to run a subquery for every row in a table, Iam not sure if Iam in the right direction, to solve my problem.

regard

Kristen
Test

22859 Posts

Posted - 2006-02-19 : 04:04:29

Hi gw, Welcome to SQL Team!

If your substring is of the form "XX9999", and particularly if it is "nc9999", then you can easily extract it from strings like "HP Compaq nc6220 (PG832EA#ABN)"

Assuming your pattern to match was "nc9999" and it was surrounded by spaces then:

DECLARE @TestString varchar(1000)
SET @TestString = 'HP Compaq nc6220 (PG832EA#ABN)'
SELECT SUBSTRING(@TestString, PATINDEX('% nc[0-9][0-9][0-9][0-9] %', ' '+@TestString+' '), 6)

Kristen

gw
Starting Member

4 Posts

Posted - 2006-02-19 : 07:29:59

Thanks for the replay Kristen,

That was a clever query, and it can cut down the number of nested IF command I use today.

But what I try to achieve, is to make a query that don't have any model name in it, but use what it find in the model_name table.

If the table have the following rows:

m700
n600
nc4010
nc6220
n800c
dc7100
nc6000
D510
2373

And I do a query that runs for every row of the table in the form "like '%prod_name%' and runs it against the long string "HP Compaq nc6220 (PG832EA#ABN)" it should only find one match. I don't know if it's possible to run a query for every row in a table.

This is why the subject is named "reversed query"

And Kristen, I have to look more into your suggestion, to know if I fully understand it, as the PATINDEX command is new to me.

Best regards,

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 02:16:28

Something like this you mean?


CREATE TABLE #MachineTypes
(
	MT_Code		varchar(10),
	MT_OtherStuff	varchar(1000),
	PRIMARY KEY
	(
		MT_Code
	)
)

INSERT INTO #MachineTypes(MT_Code)
SELECT 'm700' UNION ALL
SELECT 'n600' UNION ALL
SELECT 'nc4010' UNION ALL
SELECT 'nc6220' UNION ALL
SELECT 'n800c' UNION ALL
SELECT 'dc7100' UNION ALL
SELECT 'nc6000' UNION ALL
SELECT 'D510' UNION ALL
SELECT '2373'

CREATE TABLE #MachineNames
(
	MN_Name		varchar(100),
	MN_OtherStuff	varchar(1000),
	PRIMARY KEY
	(
		MN_Name
	)
)


INSERT INTO #MachineNames(MN_Name)
SELECT 'HP Compaq nc6220 (PG832EA#ABN)' UNION ALL
SELECT 'xxxx dc7100 yyyy'

SELECT	*
FROM	#MachineTypes
	JOIN #MachineNames
		ON MN_Name LIKE '%' + MT_Code + '%'


GO
DROP TABLE #MachineTypes
GO
DROP TABLE #MachineNames
GO

Kristen

gw
Starting Member

4 Posts

Posted - 2006-02-20 : 14:55:43

Thanks Kristen,

I guess that will work, great work!

You got the revered query working, when the tables are joining. I think I can make the first temp table from my existing MachineTypes table.

I understand now that local temporary table is very powerful, something new to learn there.

Thanks again.

tf
Starting Member

1 Post

Posted - 2006-02-20 : 16:51:47

I'm new to this stuff, so please correct me if my query is bloated/wrong or could be done simpler.

Table1:

id     int
pcname varchar(100)

Table1 fields:

id     pcname
1      NC1000
2      NR3000
3      NC3000

Query:

DECLARE @num int
DECLARE @temp varchar(100)
SELECT @num = count(id) FROM table1
WHILE @num > 0
BEGIN
	SELECT @temp = '%' + rtrim(pcname) + '%' FROM Table1 WHERE id = @num 
	IF 'COMPRAQ EVO NC3000 SUPER DUPER power 10023' LIKE @temp
		SELECT * FROM Table1 WHERE id = @num
	SET @num = @num - 1
END

Returned rows:

id     pcname
3      NC3000

gw
Starting Member

4 Posts

Posted - 2006-02-20 : 17:29:28

Thanks TF,

Your query worked just fine.

So it is possible to do a query for every row in a table.

I guess your query is a bit more a programmers approach to the problem. The code have greate potential for re-use.

Thanks again,

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 18:29:12

"please correct me if my query is bloated/wrong or could be done simpler"

The "knack" with SQL is to use set based programming, rather than loop based programming - more familiar to application programmers!

The user of loops and cursors is frequently an order of magnitude, and IME sometimes two orders of magnitude, slower in SQL.

As an application programmer myself it took me a long time to shift gears when I started using SQL in anger.

A good example of this is "splitting" a delimited list into a table of values. This can easily be visualised using a Loop based method, but joining the variable, containing the delimited list, to a "tally" table (containing contiguous numbers) is much faster! But it still blows my mind trying to understand it ...

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources