cut field length down in query

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)

cut field length down in query

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-25 : 10:37:47

Hey all,

How would I simply cut a varchar(60) field down to only a varchar(20) field length to use for linking in a query?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-25 : 10:39:56

quote:
Originally posted by hueby

Hey all,

How would I simply cut a varchar(60) field down to only a varchar(20) field length to use for linking in a query?

use Substring() function

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 10:48:55

Or the LEFT function...

LEFT(mycolumn, 20)

SUBSTRING(mycolumn, 1, 20)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-25 : 11:05:38

Thank you both for your help.

Maybe my problem is elsewhere... my code is below:

SELECT     dbo.JC_JOB_MASTER_MC.Company_Code, dbo.JC_JOB_MASTER_MC.Job_Description, dbo.JC_JOB_MASTER_MC.State, 
                      dbo.VN_GL_DISTRIBUTION_HEADER_MC.Subcontract_Job, dbo.VN_GL_DISTRIBUTION_HEADER_MC.Period, 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code, dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number, 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount, dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Cost_Type, dbo.DI_MASTER_MC.Path + dbo.DI_MASTER_MC.Filename as imagePath
FROM         dbo.DI_MASTER_MC RIGHT OUTER JOIN
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC RIGHT OUTER JOIN
                      dbo.JC_JOB_MASTER_MC INNER JOIN
                      dbo.VN_GL_DISTRIBUTION_HEADER_MC ON 
                      dbo.JC_JOB_MASTER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code AND 
                      dbo.JC_JOB_MASTER_MC.Job_Number = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Subcontract_Job ON 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code AND 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code AND 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number AND 
                      dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Type_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Type_Code ON 
                      dbo.DI_MASTER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code AND 
                      LEFT(dbo.DI_MASTER_MC.Reference, 20) = dbo.VN_GL_DISTRIBUTION_header_MC.Invoice_Number AND 
                      dbo.DI_MASTER_MC.Folder = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code
where  dbo.JC_JOB_MASTER_MC.Company_Code = 'hwp' and dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Cost_Type = 'M' and dbo.VN_GL_DISTRIBUTION_HEADER_MC.Period between '02' and '10'

What I'm trying to do is make this link: LEFT(dbo.DI_MASTER_MC.Reference, 20) = dbo.VN_GL_DISTRIBUTION_header_MC.Invoice_Number)

Do this make sense, and am I on the right path? I'm playing with this in QA, and it will only bring up a NULL value from this code: dbo.DI_MASTER_MC.Path + dbo.DI_MASTER_MC.Filename as imagePath, which should come back with values.

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-25 : 11:13:24

To dumb down the code example...

I'm using

SELECT     dbo.DI_MASTER_MC.Path + dbo.DI_MASTER_MC.Filename as imagePath, dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number, LEFT(dbo.DI_MASTER_MC.Reference, 20) as NewRef
FROM         dbo.DI_MASTER_MC RIGHT OUTER JOIN
                      dbo.VN_GL_DISTRIBUTION_HEADER_MC ON dbo.DI_MASTER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code AND 
                      LEFT(dbo.DI_MASTER_MC.Reference, 20) = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number AND 
                      dbo.DI_MASTER_MC.Folder = dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code
where VN_GL_DISTRIBUTION_HEADER_MC.Company_Code = 'HWP'

Maybe the real problem is with LEFT(dbo.DI_MASTER_MC.Reference, 20) withing the SELECT statement (I also tried SUBSTRING) because it even comes up as NULL too.

Why would it do that?

gugarc
Starting Member

17 Posts

Posted - 2006-07-25 : 13:32:10

To solve the NULL problem, you may use the ISNULL function, like that:

ISNULL(field,'') + IsNull(field,'') -- so, even qhen the fields as NULL, the concat of them will not evaluate to null (NULL PROPAGATION).

You also may use the CONCAT_NULL_YIELDS_NULL configuration (see Books online)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-26 : 03:47:42

The LEFT-function itself will not make your statement NULL, that is simply impossible. However, when you add two columns together the result would be NULL if only one of them is NULL. Try this:

DECLARE @Animal varchar(20)
SET @Animal = NULL
SELECT 'The quick brown ' + @Animal + ' jumps over the lazy dog'
SELECT LEFT('The quick brown ' + @Animal + ' jumps over the lazy dog', 19)
SELECT LEFT('The quick brown ' + ISNULL(@Animal, 'cow') + ' jumps over the lazy dog', 19)

SET @Animal = 'fox'
SELECT 'The quick brown ' + @Animal + ' jumps over the lazy dog'
SELECT LEFT('The quick brown ' + @Animal + ' jumps over the lazy dog', 19)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-29 : 05:58:36

instead of LEFT(dbo.DI_MASTER_MC.Reference, 20) = dbo.VN_GL_DISTRIBUTION_header_MC.Invoice_Number)

Try

dbo.DI_MASTER_MC.Reference like '%'+dbo.VN_GL_DISTRIBUTION_header_MC.Invoice_Number

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources