SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 left align text and add spaces at end		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hueby
Posting Yak Master

USA
127 Posts
Posted - 07/25/2006 :  16:28:22  Show Profile  Reply with Quote
Hi all,

I need to be able to link to seperate columns with the same data, just in different formats.

Table A, column1 is varchar(20), data is left aligned with spaces afterwards.

Table B, column2 is varchar(10), data is right aligned with spaces beforehand.

The data is the same, just formatted different. I need to format the data somehow so I can link it in a SQL query.

Would it be easiest to left align Table B and then fill up spaces to make 20 characters? OR...???

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 07/25/2006 :  16:50:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What type of data do the columns have? Characters or numbers?


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/25/2006 16:51:03
Go to Top of Page

hueby
Posting Yak Master

USA
127 Posts
Posted - 07/25/2006 :  17:05:12  Show Profile  Reply with Quote
They are mostly characters, with maybe one or two values have a mix of characters and a number.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 07/25/2006 :  17:17:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Then I think the fastest way is to do a one-time update first.
UPDATE TableA SET Column1 = LTRIM(RTRIM(Column1))
UPDATE TableB SET Column2 = LTRIM(RTRIM(Column2))
and then use a normal INNER JOIN.

If an update is not possible then I think a
FROM        TableA
INNER JOIN  TableB ON LTRIM(TableB.Column2) = RTRIM(TableA.Column1)
If value in fields has exact the same number of characters a join as following might be faster
FROM        TableA
INNER JOIN  TableB ON RIGHT(TableB.Column2, 6) = LEFT(TableA.Column1, 6)

Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/25/2006 17:17:46
Go to Top of Page

hueby
Posting Yak Master

USA
127 Posts
Posted - 07/26/2006 :  15:32:06  Show Profile  Reply with Quote
thank you. your suggestions helped out great.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000