need help comparing varchar and text values 1

[revilord]

I have 2 tables
one table lists a job number and the version
JOBS
Job_num Version
E-1 1.0
E-2 1.0
E-4 2.0

And a work log table
WORK
WorkInHours Description
1 E-1 Did work
2 E-4 Did more work
1 E-2 Did even more work

I need to pull out hours of work for version 1.0 jobs.

something like
select WorkInHours from WORK
where Description IN (
select Job_num form JOBS
where Version = 1.0)

This doesn't work because Description is a text field and the job numbers are like 'E-1' but the Description is 'E-1 Did work'.

and I can use LIKE since it won't except more then one value.

Any suggetions

 

[SQLDenis]

your design is horrible, your work table should have a job_num column. Where is your referential integrity. why are you using texct columns for data that is less than 80 characters? varchar can handle up to 8000.

good luck when this table grows, your performance will be horrible with a capital H

so here is a kludge assuming Job_num is always 3 charaters


select * from jobs j
join WORK w on j.Job_num = left(convert(varchar(8000),w.[Description]),3)
where j.Version = 1.0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Totally agree with Denis.
You must add a JOB_NUM column in Work table.
made a description field to be varchar(8000)
in SQL 2005 you could use varchar(max) with is UP to 16 MB.
Then you query will look like this:

SELECT Work.WorkInHours
FROM Jobs
INNER JOIN Work ON Jobs.Job_Num = Work.Job_Num
WHERE Jobs.Version = 1.0

Isn't it beautiful?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>in SQL 2005 you could use varchar(max) with is UP to 16 MB.

it's actually 2 Gigabytes not 16MB

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

From where these 16 MB comes to me?
But aren't 16 MB enough?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>From where these 16 MB comes to me?

My guess... 16bit pointer to the text data inside the table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

maybe you are right.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[revilord]

Thanks guys. I know it's bad design but I am converting from VSS and a corporate help desk to SVN and JIRA. However, I'll have Microsoft, Tortise, and Atlassian better cordinate there table structures.

There are thousands of Jobs. however the sturcture of the description is always the same.
Job number+space+work description.

I guess what I am asking is how do I pull out the Job number from the description and compare it to the Job number from the job table.

 

[SQLDenis]

>>I guess what I am asking is how do I pull out the Job number from the description and compare it to the Job number from the job table.


?? did you read my answer at 16 Nov 06 11:55 ? I gave you a query

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Then Denis's first answer is the way.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[revilord]

Doesn't Denis assume the job number is 3 characters. I mentioned there are thousands of jobs and therefore it can't be 3 characters.

Is there a function that will dump everything after the first space?

 

[SQLDenis]

Yes, and you owe a star to George (

http://www.tek-tips.com/userinfo.cfm?member=gmmastros)

for this

Select *
From Jobs J
Inner Join WorkLog W
On J.JobNum = SubString(W.Description, 1, Len(J.JobNUm))
Where J.Version = 1.0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Try this instead.

Select * 
From   Jobs J 
       Inner Join WorkLog W 
         On J.JobNum = SubString(W.Description, 1, Len(J.JobNum))
Where  J.Version = 1.0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

But like I said before your performance is going to suck big time. for every search a table scan will be used. SQL server will ingore any index you have on that table. Better fix it now and add an additional column to the work table or suffer later

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Based on Denis' code

select *
       from jobs j
       join WORK w 
            on j.Job_num = left(convert(varchar(8000),w.[Description]),CHARINDEX(' ',w.[Description])-1)
where j.Version = 1.0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Denis,

How is it possible that you were able to post my code before I did?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

come one 'George', tell people the truth. we are the same person, we have 2 computers the left computer is used by 'denis' the right computer is used by 'george'



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[revilord]

Thansk, that did it George. If you could see me you would have seen me jump out of my seat. Thanks to Borislav and Denis too. I guess it's all in asking the right question.

 

[revilord]

The good new is that the query ran great. But it took 33 minutes to return 11,000 rows. thankfully, although neccessary, I only need to run it infrequently.