left align text and add spaces at end

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)

left align text and add spaces at end

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-25 : 16:28:22

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

30421 Posts

Posted - 2006-07-25 : 16:50:20

What type of data do the columns have? Characters or numbers?

Peter Larsson
Helsingborg, Sweden

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-25 : 17:05:12

They are mostly characters, with maybe one or two values have a mix of characters and a number.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 17:17:17

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

hueby
Posting Yak Master

127 Posts

Posted - 2006-07-26 : 15:32:06

thank you. your suggestions helped out great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources